The most frequent text

In the pre­vi­ous post, I was talk­ing about a dom­i­nant, which is the most fre­quent num­ber. Today, we are going to find the most fre­quent text.

This task is a bit hard­er, because the MODE func­tion that we used pre­vi­ous­ly needs num­bers. It means that we have to con­vert text into num­bers. To do so, we can use the MATCH func­tion. It means that we will be look­ing for our whole text in our whole text. We want the exact match (Fig. 1)

While using the MATCH func­tion, Excel starts cal­cu­la­tions from the top. It means that each pup­py in the range is refer­ring to the first one on the list, which is on the first posi­tion is pre­sent­ed as 1 (Fig. 2)

Look­ing at the sec­ond exam­ple, the first kit­ten is in posi­tion num­ber 2, so each kit­ten will refer to the same num­ber (Fig. 3)

Now, that we have our text con­nect­ed to num­bers, we can add the MODE.MULT func­tion to return the most fre­quent num­bers, i.e. posi­tions of text (Fig. 4)

=MODE.MULT(MATCH(A2:A9,A2:A9,0))

And we have the results (Fig. 5)

Since we have the posi­tions, we can add the INDEX func­tion to return text con­nect­ed to those posi­tions (Fig. 6)

=INDEX(A2:A9,MODE.MULT(MATCH(A2:A9,A2:A9,0)))

Just like that we have our solu­tions in the Dynam­ic Array Excel (Fig. 7)

In the clas­sic Lega­cy Excel, we should select more cells and use the Ctrl + Shift + Enter key com­bi­na­tion to put our results in all cells (Fig. 8)

How­ev­er, we are in DA Excel, so we have a sim­pler solu­tion. At the end, we want to check what hap­pens when the text appears only once in a range. We can see that the MODE.MULT func­tion returns the #N/A error (Fig. 9)

When text appears the same num­ber of times, in our case it’s cat and dog, the MODE.MULT func­tion will return those results (Fig. 10)

https://www.youtube.com/watch?v=MpdrAz_ZNdo