Extracting information for a series of top 5 records

Question

I'm using XL2007 and have a worksheet of about 200 rows each with about 30 columns of data.

In column C is a value in the range A to K and in column D is an integer which could be from about 300 to 5000.

What I want to be able to do is show in another spreadsheet the data for the top 5 values in column D for each value in col C (ie top 5 values for A, top 5 values for B, etc.)

In the new worksheet, I'd like to show columns A, B and D of the first worksheet (cols A and B is short text)

Can you help?

Rob

Answer

Use Large Function in Sheet 2 and apply it for the Numeric Data of Sheet1.

To get the First Largest Value =LARGE(Sheet1!D:D,1)

To get the Second Largest Value =LARGE(Sheet1!D:D,2)

To get the Third Largest Value =LARGE(Sheet1!D:D,3)

Like the above change the number 3 to 4 in the above formula for getting the fourth largest number.

Now apply vlookup or Lookup and retrieve the data from sheet1 based on the large formula result