Read the top three items from a goods list

Hi

Let's say you have a column that contains a random list of different types of veg. How would I list the top three items from the list in another 3 cells titled 1st, 2nd & 3rd?

For example, let's say the column lists 5 apples, 3 bananas, 3 plums, 2 pears and a grapefruit. The "1st" cell should say "Apples", the "2nd" cell "Bananas" (tied with plums but sorted alphabetically) and the "3rd" cell "Pears". I can do the counts ok with COUNTIF but getting them in order is proving troublesome.

thanks
Andy

Does this help?

http://www.excelforum.com/excel-form...king-info.html

AndyGW,

Attached is an example workbook based on the criteria you described.
In sheet 'Data' is the list of Fruits in a random order.
That information is used to create the pivot table shown on sheet 'Pivot Table'

The pivot table is with:
Row Label = Fruits
Σ Values = Count of Fruits

Then in sheet 'Pivot Table' I selected cell B2 (which is the first non-pivot table cell that is directly above the "Total" column) and went to Data -> Filter
After that, I was able to use the Filter on Total to select "Number Filters" -> "Top 10..." -> changed from 10 to 3 -> OK

The pivot table is now showing the top three count of fruits.

You should be able to right-click the pivot table and select "refresh"

I can but that doesnt solve the problem. Let me clarify. The pivot table reads the information contained in my data column. From that pivot table 3 other cells are populated with the 1st 2nd and 3rd highest appearances in the column. When I calc the sheet, the data column is populated but the pivot table does not update. Thus the 1st, 2nd & 3rd cells show old data. Sure I can update the pivot table but that doesn't make the 1st-3rd cells update, they still read wrongly. Only by hitting f9 do they update but then of course so does my data column and we're back to square one.

At this point I would need an example workbook to better understand what your requirements are so...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.