# Count items return which occurs most

#### waveout

##### New Member
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

##### Well-known Member
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

##### New Member
Thanks, I will give this a try!

##### Well-known Member
Try this:

Excel Workbook
ABCD
1LISTFrequencyMax
2Apple
3Orange2Orange
4Strawberry1
5Orange
6Apple
7Apple
8Tomato1
 Sheet1

#### waveout

##### New Member
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

Apple
Orange
Orange
Banana

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

#### facethegod

##### Well-known Member
Your List in A2:A12, put this formula in B2 and drag down

Code:
``````=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

##### New Member
facethegod,
Thanks! That code worked perfectly!

1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...