Find most common word in a column and Least common word.

[savosean]

I have a List where users will be inputting the province they are from where each province has a different rate associated to it. What I am trying to do as of now, I need my customers to manually input the Provinces twice. Once in a Column on one page, and another time on another.

I'd like to find the most common word in my column, so that my cell on the other sheet simply references this, and as well with the least common. As the customer will only ever have a maximum of two different provinces being chosen.

As of now I have this

=INDEX($I$9:$I$24,MATCH(MAX(COUNTIF($I$9:$I$24,$I$9:$I$24)),COUNTIF($I$9:$I$24,$I$9:$I$24),0))

=INDEX($I$9:$I$24,MATCH(MIN(COUNTIF($I$9:$I$24,$I$9:$I$24)),COUNTIF($I$9:$I$24,$I$9:$I$24),0))

and then I ctrl+alt+enter to make it an array... sadly I can't get it to work. I simply get an error.

I've been researching to find a solution to no avail... Any help would be greatly appreciated it. If you need more clarification just let me know.

Thanks.

 

[savosean]

I've actually gotten the finding of the most common to work a little...

=IFERROR(INDEX(I9:I24,MODE(IF((I9:I24<>"")*ISNA(MATCH(I9:I24,$K$30,0)),MATCH(I9:I24,I9:I24,0)))),"N/A")<strike></strike>

ctrl+alt+enter for Array

the problem is I still don't know how to find the least common. And if I have an even number lets say 2 ON's and 2 AB's how would I get it to work? Currently if it's an even number it will show "N/A" as it's an error.

 

[savosean]

Sorry it will not show up as an error, it will simply show the first result. Which is perfect for the most common one. But, if I end up with 2 ON's and 2 AB's. I would need the the other cell that would have the least common frequency word to showcase the other Province.

 

[Rick Rothstein]

Here are two normally entered formulas that might work for you...

MAX
-------------
=IF(COUNTIF(I9:I24,I9)=COUNTIF(I10:I24,"<>"&I9),"EQUAL",IF(COUNTIF(I9:I24,I9)>COUNTIF(I10:I24,"<>"&I9),I9,INDEX(I1:I24,MIN(IF(I9<>I10:I24,ROW(I10:I24))))))

MIN
-------------
=IF(COUNTIF(I9:I24,I9)=COUNTIF(I10:I24,"<>"&I9),"EQUAL",IF(COUNTIF(I9:I24,I9)<COUNTIF(I10:I24,"<>"&I9),I9,INDEX(I1:I24,MIN(IF(I9<>I10:I24,ROW(I10:I24))))))

Note: I wasn't sure what to do if both provinces counted equally, so I simply outputted the word "EQUAL".

 

[savosean]

Thank you for the reply appreciate the help, the first formula seems to bring out the least frequent value. From there it doesn't change, if I continue to update the column it remains static.

The second formula is giving me an error, and I don't know where the typo is to resolve it.

The formula

=IFERROR(INDEX(I9:I24,MODE(IF((I9:I24<>"")*ISNA(MATCH(I9:I24,$K$30,0)),MATCH(I9:I24,I9:I24,0)))),"N/A")

works at finding the maximum as I need, the problem is it won't count the value if it's just 1. I.E: someone puts only one province in ON. the cell will remain on the N/A error. Is there a way to make it show the text even if it's just 1 entry?

And how can I alter this formula to work for the least frequent province being inputted?

I may have forgot to mention that the column is continuously updated, and therefore I need the formula to work dynamically as the column is updated.

Thanks

 

[Rick Rothstein]

Thank you for the reply appreciate the help, the first formula seems to bring out the least frequent value. From there it doesn't change, if I continue to update the column it remains static.

I built my formula based on the formula you posted in Message #1 which only referenced the range I9:I24, but now I see your formula is also referencing cell K30... can you explain to us what that is about?

I may have forgot to mention that the column is continuously updated, and therefore I need the formula to work dynamically as the column is updated.

Yes, you did forget to mention that. What about the cell K30 reference I asked about above... will that change or expand dynamically also?

 

[savosean]

The reason I reference K30, is to make sure the formula works even if the column has empty cells. Basically K30 is a cell that is empty.

As I continue to work on this I've gotten to this point

=IFERROR(IF(COUNTA(I9:I24)=1,I9:I24,INDEX(I9:I24,MODE(IF((I9:I24<>"")*ISNA(MATCH(!$K$30,0)),MATCH(!I9:I24,I9:I24,0))))),"N/A")

Now it's taking the province if only one is entered, the problem I run into is if only two provinces are entered only once each. It will become an error. Although, if further down the road the province numbers even out it will show the first province entered which is what I want. So I am simply stuck at trying to find a way to have it input the first province entered if only 2 different provinces are entered within my column.

Then I need to find a way to flip this formula and show the least frequent province.


EDIT: I've gotten it all to work for the MAX now with this formula below

=IFERROR(IF(COUNTA(I9:I24)=1,I9:I24,IF(COUNTA(I9:I24)=2,I9:I24,INDEX(I9:I24,MODE(IF((I9:I24<>"")*ISNA(MATCH(!$K$30,0)),MATCH(!I9:I24,I9:I24,0)))))),"N/A")

Only problem remaining is reversing this and to attempt to showcase the Least frequently found province in my column.

 

[savosean]

Basically trying to find an opposite function to the MODE one so I can return the least frequent occurring province.

 

[savosean]

=IFERROR(IF(COUNTA(I9:I24)=1,I9:I24,IF(COUNTA(I9:I24)=2,I9:I24,INDEX(I9:I24,MODE(IF((I9:I24<>"")*ISNA(MATCH(!$K$30,0)),MATCH(!I9:I24,I9:I24,0)))))),"N/A")QUOTE]

My formula seems to break if the province is inputted anywhere but the first row and down. If I input my data near the middle lets say, the formula will return my IFERROR. Any help would be appreciated

 

[savosean]

If anyone in the future comes to this thread for a similar question I found the solution to my problem...

Simplified it by a large margin. Create a New Table further down, and did a Count if for each possible Province. From there simply associated each count if with the respective province. Ran a Max formula for the most frequent and a Min Array formula for the least common. Formulas will be below for anyone's future reference.

Keep in mind C132:C144 is my Countif's of each province while D132:D144 are my provinces.

For the MAX :

=MAX(C132:C144) placed in cell C146

Proceeded by

=IF(C146=0,"N/A",VLOOKUP(C146,C132:D144,2,FALSE))

For the Min :

=MIN(IF(C132:C144>0,C132:C144)) *Array Formula. PLaced in Cell147

Proceeded by

=IFERROR(IF(C147=0,"N/A",IF(C147=C146,INDEX(D132:D144,SMALL(IF(C132:C144=C147,ROW(C132:C144)-ROW(C132)+1,ROW(C144)+1),2),0),VLOOKUP(C147,C132:D144,2,FALSE))),"N/A") *Array Formula