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?
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