Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
    colUnique.Add rCell.Value, CStr(rCell.Value)
    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
  •