Count items return which occurs most

waveout

New Member
Joined
Apr 4, 2006
Messages
15
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
Joined
Apr 22, 2009
Messages
624
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.
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
Try this:

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

waveout

New Member
Joined
Apr 4, 2006
Messages
15
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

Well-known Member
Joined
Aug 1, 2006
Messages
767
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!
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top