Return cell address of value in array

[Dave Reed]

Excel 97

A 40 row two column list of items made up of (col a) a text label (say apples & (col b) a count of the occurances of the text label in an external table. - all ok upto this point

Need to identify the top 10 text counts, so say the most common item occurs 15 times, I can identify this using the large function, what I cannot do is tell what the number refers to, so if I was able to return the cell address the large function got the number from I could work it out.

This also need to be able to handle duplicates (ie a count of 2 in 5th & 6th position).

 

[lenze]

Hi and welcome to the Board!!!
Have you considered a Pivot Table??

lenze

 

[Dave Reed]

I was hoping to be able to do it with a function, thinking along the lines of excel must know the cell address it took the value from, just want it to return that cell address.

had not thought of a pivot table - will check out.

Thanks for the fast reply from the other side of the water

 

[Dave Reed]

Tried pivot table does not do the required business unfortunately