How to display Col1 and Col5 for the largest 3 values of Col5

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I posted this question earlier but I did a poor job of explaining my
situation. Thank you Aladin for your answer but I did not really
understand it. Please let me try again with some actual examples of
my data.

On sheet1 users key in various statistics about proposed database
objects and it calculates the actual size of the database object.
There may be hundreds of records here in no particular order.

name, num cols, row length, num rows, <calculated size>
name, num cols, row length, num rows, <calculated size>
name, num cols, row length, num rows, <calculated size>
name, num cols, row length, num rows, <calculated size>
name, num cols, row length, num rows, <calculated size>

Sheet2 creates a summary of the data in Sheet1. One of the things I
want to list here are the names of the top 3 largest objects and
corrosponding calculated sizes from Sheet1. This is causing me
problems.

Here is a sample of my data followed by what I want to do…

SHEET1:(Database Object Statistics - last field is the calculated
size)

"POS_MARKDOWN_DETAIL","12","157","5250000","1"
"ON_HAND_DELTA","7","76","75000000","8"
"EXPEDITOR","27","455","125000000","61"
"SALES_DETAIL","13","171","1700000000","577"
"TRANSFER_TRANS","32","240","34200000","61"

Here is what I want to display on the next sheet…
SHEET2:(Top 3 largest objects - based on calculated size - in
descending order)

"SALES_DETAIL","577"
"EXPEDITOR","61"
"TRANSFER_TRANS","61"

I am currently doing this with an INDEX(MATCH(LARGE())) function and
it works fine UNLESS 2 or more of my top 3 largest objects have the
SAME calculated size. If that happens then I get this…

"SALES_DETAIL","577"
"EXPEDITOR","61"
"EXPEDITOR","61"

As you can see, it lists the first object with a duplicate size twice.
I don't know how to get around this.

As I stated before, I'm an excel newbie so please be as detailed as
you can with your suggestions. Thanks in advance!

Roger
 
Roger,

To get rid of ties, you can use another column, where you simply add a
small unique value to the size, then do your matching based on that.
For example, if your size is in E2, then in F2, use the formula
=E2 + ROW()/100000000

If you don't want to use another column, then change the formula in
your size field.

HTH,
Bernie
MS Excel MVP
 
Back
Top