Count items return which occurs most

[waveout]

Using the following data (this list could be several thousand lines long):

Apple
Orange
Strawberry
Orange
Apple
Apple
Tomato

I would like to count and return the item that occurs the most (Apple) then the one that returns 2nd most (Orange). If item only occurs once then it can be ignored.

Results would look like this:
Apple
Orange

 

[delaneyjm]

A pivot table is ideal for this.

If you prefer a formula solution, you'll need to do a couple of things first. Use the Advanced Filter to generate a list of unique items that will be pasted to a an adjacent column.

Once you have the list of unique values, use this formula:

=COUNTIF($A$1:$A$100,C1)

where cells A1 to A100 are your list of values, and C1 is the first unique value. You can adjust the cell ranges to your worksheet.

I would recommend the Pivot Table solution over the formula. But if you require additional help, just post back.

 

[waveout]

Thanks, I will give this a try!

 

[asad]

Try this:

Excel Workbook
	A	B	C	D
1	LIST	Frequency		Max
2	Apple	3		Apple
3	Orange	2		Orange
4	Strawberry	1
5	Orange
6	Apple
7	Apple
8	Tomato	1
Sheet1

 

[waveout]

asad,
Thanks for the help. I was able to use your code and it worked well until I have two items that occur the same number of times. It just repeats the first one twice.

Orange
Apple
Strawberry
Apple
Apple
Orange
Banana
Orange
Apple
Strawberry
Strawberry
Banana

Had the following results
Apple
Orange
Orange
Banana

The second Orange should of been Strawberry. Is this possible?

 

[facethegod]

Your List in A2:A12, put this formula in B2 and drag down

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",
INDEX($A$2:$A$12,MODE(IF(COUNTIF($A$2:$A$12,$A$2:$A$12)>1,
IF(ISERROR(MATCH($A$2:$A$12,$B$1:B1,0)),MATCH($A$2:$A$12,$A$2:$A$12,0)))))))

This is an array formula, so you confirm with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[waveout]

facethegod,
Thanks! That code worked perfectly!