Finding Most Common Values in Dataset

[QuarterTurn]

I'm trying to find the most common values in a set, where multiple values can occur in each cell.

I have a series of values organized similarly to the list below

ex.1

Apple
Orange|Pear
Apple|Pear
Apple|Orange|Pear
Watermelon|Pear
Apple|Pear
Strawberry|Orange​

and as a second example:

ex.2

Apple
Apple|Orange
Apple
Orange
Apple
Apple
Apple​

I'm trying to find the most common value, the second most common value, and the third most common value for each set, but only if that value appears at least three times.

In example 1, I am looking to see Pear as the most common answer, then Apple, and then Orange
In example 2, I am looking to see Apple as the most common answer, and for Orange to be ignored, as it does not appear the minimum three times.

I tried using a formula =INDEX(A1:A7,MODE(MATCH(A1:A7,A1:A7,0)))
but that would only read the value of the cell as a whole. (in example 1, it would feed "Apple|Pear" because that's the most common exact match).
I don't know how to write a formula that scans multiple values per cell. And I don't know how to restrict it to a minimum of three occurrences. And I don't know how to find the second or third most common values.

I hope I've been clear in my explanation. This is my first time writing in to Mr.Excel.
I'm happy to add any clarification.

Thank you.

Using Excel for Mac 2011

 

[ndsutherland]

Not sure if this will work on a Mac, but try this UDF

Function ModeString(rng As Range, d As String, k As Integer)
Dim Fruit As String, FruitArr() As String
Dim r As Range


For Each r In rng
    If Len(Fruit) = 0 Then Fruit = r.Value Else Fruit = Fruit & d & r.Value
Next r


For i = 1 To k
    FruitArr = Split(Fruit, d)
    ModeString = Application.Index(FruitArr, Application.Mode(Application.Match(FruitArr, FruitArr, 0)))
    Fruit = Replace(Fruit, ModeString & d, "")
Next i


End Function

with this function

=ModeString(Range,d,k)

where d is the delimiter ("|" in this case) and k is mode rank

a tie will return the first occuring

 

[ndsutherland]

I made a few tweaks

Function ModeString(rng As Range, d As String, k As Integer)
Dim Fruit As String, FruitArr() As String, fruitcount as Integer
Dim r As Range


On Error GoTo NA
For Each r In rng
    If Len(Fruit) = 0 Then Fruit = r.Value Else Fruit = Fruit & d & r.Value
Next r

For i = 1 To k
    FruitArr = Split(Fruit, d)
    ModeString = Application.Index(FruitArr, Application.Mode(Application.Match(FruitArr, FruitArr, 0)))
    Fruit = Replace(Fruit, ModeString & d, "")
Next i

For i = 0 To UBound(FruitArr)
    If FruitArr(i) = ModeString Then fruitcount = fruitcount + 1
    If fruitcount > 2 Then Exit Function
Next i


NA:
ModeString = "N/A"
End Function

 

[QuarterTurn]

Thank you so much for your response. I am not too familiar with Macros, so I don't know how to run this.

Are you able to help guide me through how to implement this code, or otherwise provide a formula alternative?

 

[ndsutherland]

Here is some info on VBA for Mac Excel 2011

I'll continue working on a formula solution, but it will be very complicated and probably be one of the longest formulas you have ever seen.

 

[QuarterTurn]

Thank you! I will look through these tutorials and see if I can figure out VBA.

Keep me posted if you figure out a formula. I really appreciate all your help.

 

[ndsutherland]

All right. I have a formula that finds the largest, but I have not yet figured out how to get the 2nd, 3rd etc (the usual method involves count or countif) or how to apply the 3 limit

=INDEX(TRANSPOSE(INDEX(IFERROR(MID(A1:A7,SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1))),SEARCH(CHAR(127),SUBSTITUTE(A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))-SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))),""),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1)/COLUMNS(A1:F1))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1),COLUMNS(A1:F1))))))),MODE(IFERROR(MATCH(INDEX(IFERROR(MID(A1:A7,SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1))),SEARCH(CHAR(127),SUBSTITUTE(A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))-SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))),""),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1)/COLUMNS(A1:F1))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1),COLUMNS(A1:F1)))))),TRANSPOSE(INDEX(IFERROR(MID(A1:A7,SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1))),SEARCH(CHAR(127),SUBSTITUTE(A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))-SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))),ROW(INDIRECT("A1:A"&ROWS(A1:A7)*COLUMNS(A1:F1)))),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1)/COLUMNS(A1:F1))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1),COLUMNS(A1:F1))))))),0),""))) confirmed with CTRL+SHIFT+ENTER

This is really the same concept of the formula you listed in post #1 =INDEX(A1:A7,MODE(MATCH(A1:A7,A1:A7,0)))

MID(A1:A7,SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1))),SEARCH(CHAR(127),SUBSTITUTE(A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))-SEARCH(CHAR(127),SUBSTITUTE("|"&A1:A7&"|","|",CHAR(127),COLUMN(A1:F1)))) This creates a 7x7 array of the separate words in your range (7 columns allows for up to 6 words in the corresponding cell. For your example you really only need 3)

N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1)/COLUMNS(A1:F1))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A7)*COLUMNS(A1:F1)))-1),COLUMNS(A1:F1))))))) This redimensions that array to 1 column x 49 rows

Transpose allows me to index or match the subsequent array

Here is an example so you can check it out. I don't think this formula is very practical or useful, and I'm still not sure it will work on a mac

 

[QuarterTurn]

Wow, this is brilliant! I tested it on a Mac with my example and it worked perfectly. It is actually very practical and useful for the projects I'm working on.

I really cannot thank you enough for your help with all this.

 

[Jim Gordon]

If it were me, I'd make a pivottable then filter the table to show values greater than or equal to 3.