# Thread: Find most popular item (2003)

1. ## Find most popular item (2003)

Hopefully there is a simple function to do this - I have a column of text listing CD, Video, DVD and other categories. I want a formula to tell me directly which category is most popular. I.e. a combination of finding the most 'popular' category and telling me its name? Andy.

2. ## Re: Find most popular item (2003)

A custom function perhaps, I don't think there is anything "built-in"

You can use a pivot table get the results of them and their number. If you have it sorted the top item is the item you are looking for...

Steve

3. ## Re: Find most popular item (2003)

I know it's possible to find the item by, for example, creating several countif functions and then sorting this list. But hopefully it can be achieved in one function. Andy.

4. ## Re: Find most popular item (2003)

Say you have data in A1:A28.
Create a listing of unique values in column E, say E1:E3 contain CD, DVD and Video, respectively.
In F1, enter the formula =COUNTIF(\$A\$1:\$A\$28,E1) and fill down to F3.
The following formula will display the most popular category: =INDEX(E1:E3,MATCH(MAX(F1:F3),F1:F3,0))

If you want to avoid the auxiliary table E1:F3, you can use this formula:

=INDEX({"cd";"dvd";"video"},MATCH(MAX(COUNTIF(A1:A 28,{"cd";"dvd";"video"})),COUNTIF(A1:A28,{"cd";"dv d";"video"}),0))

5. ## Re: Find most popular item (2003)

Hi Andy

No built in function that I am aware of but I have created this excel sheet using standard functions.

In Column A I have listed CD, Tape and Video.
In Column C and D I have created an index
Column F used the countif function to count each type of media in the list and next to them

in Column G a VLookup which tells you the counts of each media type.

You could hide the columns or put them in a distant cell in the worksheet. my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth

6. ## Re: Find most popular item (2003)

Here is a function I alluded to.
<pre>Option Explicit
Function MostPopular(rng As Range)
Dim colUnique As New Collection
Dim rCell As Range
Dim x As Long
Dim lUnique As Long
Dim iList() As Integer
Dim iMax As Integer
Dim sMax As String

On Error Resume Next
For Each rCell In rng
Next
On Error GoTo 0
lUnique = colUnique.Count
ReDim iList(1 To lUnique)
For x = 1 To lUnique
iList(x) = 0
Next
iMax = 0
For Each rCell In rng
For x = 1 To lUnique
If UCase(rCell.Value) = UCase(colUnique(x)) Then
iList(x) = iList(x) + 1
If iList(x) > iMax Then
iMax = iList(x)
sMax = colUnique(x)
End If
Exit For
End If
Next
Next

MostPopular = sMax
End Function</pre>

Add it into a module and call it (eg with):
<pre>=MostPopular(A1:A25)</pre>

and it will list the most popular item.

To get the number of the most popular you can use:
<pre>=COUNTIF(A1:A25,mostpopular(A1:A25))</pre>

If there is a tie (or ties) the item selected will be the item that is the last unique item added to the list.
Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•