Most frequently used word - subject to a particular filter

[Excelina1234]

Hi All,

If you know about Index, Mode, Match ("IMM") as an alternative to finding the most frequently used piece of *text* in a particular range (as opposed to simply using mode for a range comprising of *numbers*) you may be able to help me!

What I am trying to do is take IMM to the next level. Essentially, I have a range of text cells which correspond to different categories (illustrated below):

Category Text
France Train
France Bike
France Train
France Train
Belgium Bike
Belgium Bike
Belgium Bike

So - although Bike is the most frequent word - I want to retrieve the most frequent word for *France only* (in this case Train).

Is there any way we can develop the IMM formula to make it more specific so as to retrieve the word Train for France and Bike for Belgium?

Thanks a lot!

 

[Andrew Poulsom]

Does this work for you (formula in D1 copied down)?

Excel 2010
	A	B	C	D
1	Category	Text	France	Train
2	France	Train	Belgium	Bike
3	France	Bike
4	France	Train
5	France	Train
6	Belgium	Bike
7	Belgium	Bike
8	Belgium	Bike
Sheet1

Cell Formulas
Range	Formula
D1	{=INDEX(B$2:B$8,MODE(IF(A$2:A$8=C1,MATCH(A$2:A$8&B$2:B$8,A$2:A$8&B$2:B$8,0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Excelina1234]

For some reason, I get #N/A when I press Ctrl+Shift+Enter

The example I gave was simplified. In my actual formula, I have named the ranges EB_Client and EB_Geography (i.e. I want to know the most travelled to geography for a given client). This is the formula I used. Any chance you can spot what's wrong with it?

=INDEX(EB_Geography,MODE(IF(EB_Client=E3,MATCH(EB_Client&EB_Geography,EB_Client&EB_Geography,0))))

Thanks so much!

 

[Andrew Poulsom]

You can see that my formula works from the sample I posted. What do your names refer to? Your formula works for me:

Excel 2010
	A	B	C	D	E	F
1	Category	Text
2	France	Train
3	France	Bike			France	Train
4	France	Train			Belgium	Bike
5	France	Train
6	Belgium	Bike
7	Belgium	Bike
8	Belgium	Bike
Sheet1

Cell Formulas
Range	Formula
F3	{=INDEX(EB_Geography,MODE(IF(EB_Client=E3,MATCH(EB_Client&EB_Geography,EB_Client&EB_Geography,0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
EB_Client	=Sheet1!$A$2:$A$8
EB_Geography	=Sheet1!$B$2:$B$8

 

[WarPigl3t]

I have not even the slightest clue how to accomplish this using excel built in formulas. Here's a macro instead.

Function myFunction(mySearch, myRange)
    a = -1
    Application.Volatile
    For Each Cell in myRange
        mySplit = SPLIT(Cell, " ")
        output = FALSE
        For Each myValue in mySplit
            If myValue = mySearch Then
                output = TRUE
            End If
        Next myValue
        If output = TRUE Then
            For Each myValue in mySplit
                If mySearch <> myValue Then
                    a = a + 1
                    ReDim Preserve myArray(a) as String
                    myArray(a) = myValue
                End If
            Next myValue
        End If
    Next Cell
    a = 0
    For Each myValue in myArray
        ReDim Preserve myUnique(a) as String
        output = TRUE
        For Each item in myUnique
            If item = myValue Then
                output = FALSE
            End If
        Next item
        If output = TRUE Then
            myUnique(a) = myValue
            a = a + 1
        End If
    Next myValue
    countHolder = 0
    For Each myValue in myUnique
        counter = 0
        For Each item in myArray
            If item = myValue Then
                counter = counter + 1
            End If
        Next item
        If counter > countHolder Then
            countHolder = counter
            holdString = myValue
        End If
    Next myValue
    myFunction = holdString
End Function

This is by far the most complicated code I ever wrote. It is untested but give it a try. After you copy paste this code into your VBA macros, all you have to do is insert a formula into whatever cell you want to activiate it. So lets say you want the output to go in cell B1. Just type in this formula into cell B1.

=myFunction("France", A2:A100)

A2:A100 is just a random range I used but make sure to include all the cells in your real range.

 

[Excelina1234]

Interesting. I can see that it works on your sheet so I must be doing something wrong. Did the { and } brackets appear automatically when you pressed ctrl+shift+enter?

 

[Excelina1234]

@WarPiglet - thanks a lot for your help. Hopefully I can figure out what I'm doing wrong but Andrew's answer is more like what I was looking for. Thanks anyway!

 

[Excelina1234]

Andrew! It worked! Thanks so so much and sorry about the back and forth - really appreciate your help!

 

[Excelina1234]

Andrew sorry I only have one more question. Is it possible to make it so that we don't have to press ctrl shift enter every time we change the "filter" category (i.e. the client) in the formula? Essentially, I have set up the sheet so that there is the activex combobox (which you also showed me how to use) which allows me to pick clients. A whole bunch of formulas are linked to that client name cell - including this one for the most common geography. All the other formulas update automatically but this one doesnt. Any chance you know how I could tweak that?
Sincere apologies for the trouble!

 

[Andrew Poulsom]

The formula should refresh automatically if calculation is set to automatic. You shouldn't need to edit it.