Ranking recurring words in an autofiltered column

[Mike U.]

I'm trying to figure out how to identify, within an
autofiltered column, the most frequently occuring word;my
next step is to then identify the second and thrid most
recurring words.

Example:
Black
Black
White
Green
Green
Black

1 = Black
2 = Green
3 = White

Any help that can be provided would be much appreciated.

Thanks!

 

[Gary Thomson]

OK here goes,

Have your existing data in Column C.

Then in cell B1, type this formula:

=COUNTIF($C$1:$C$100,C1)

and copy this down column B as far as your data goes.

Now, in cell A1, type this formula:

=VLOOKUP(MAX($B$1:$B$100),$B$1:$E$100,2,FALSE)

and this will return the colour that appears the most.

Now, in cell E1, type this formula:

=IF(C1=$A$1,"",C1)

and again copy this down as far as your data in column C
went.

Now, in cell D1, type the formula:

=COUNTIF($C$1:$C$100,E1)

and copy down.

now in cell A2 type:

=VLOOKUP(MAX($D$1:$D$100),$D$1:$E$100,2,FALSE)

which will return the second colour that appears the most.

So what you will have to do is continue with the same
pattern:

Two columns (next will be F and G) which eliminate
previous high-ranked colours (column G) and then total up
the entries for the remaining colours (column F);

then an entry in column A to pick out this value (next one
will be in cell A3, and will look be:

=VLOOKUP(MAX($F$1:$F$100),$F$1:$G$100,2,FALSE)

note that if you have mroe than 100 entries in Column C
you will need to adjust the ranges to allow for this.

Regards,

Gary Thomson.

 

[Jonathan Rynd]

I'm trying to figure out how to identify, within an
autofiltered column, the most frequently occuring word;my
next step is to then identify the second and thrid most
recurring words.

Suggestion:
1. associate each word with a number
2. create a new column that uses MATCH to pick the appropriate number
3. Use FREQUENCY to get a count of each number.

But your best bet is probably to write a UDF in VBA that does the
equivalent of FREQUENCY on strings.

 

[Jonathan Rynd]

=VLOOKUP(MAX($B$1:$B$100),$B$1:$E$100,2,FALSE)

If you replace MAX($B$1:$B$100) with LARGE($B$1:$B$100,n) where n is an
number, you get the nth largest number in the list. No need to go through
the manipulations with the extra columns and formulas.

 

[hgrove]

Mike U. wrote...

I'm trying to figure out how to identify, within an autofiltered
column, the most frequently occuring word;my next step is to
then identify the second and thrid most recurring words.

Example:
Black
Black
White
Green
Green
Black

1 = Black
2 = Green
3 = White

If your column weren't filtered and your topmost result cell were B1
you could use formulas like

=INDEX(List,MATCH(LARGE(FREQUENCY(MATCH(List,List,0),
ROW(List)-CELL("Row",List)+1),ROW()-ROW(B$1)+1),
FREQUENCY(MATCH(List,List,0),ROW(List)-CELL("Row",List)+1),0))

However, there's no way to do this with filtered data unless you us
the filter values from other columns in the formula. Or use VBA

 

[Aladin Akyurek]

Let A2:C18 house on Sheet1 the following sample:

{"X","Y","Color";
4,"bob","black";
7,"bill","white";
8,"bob","white";
6,"fran","black";
2,"brian","green";
2,"fran","green";
8,"damon","green";
4,"dick","white";
4,"theodore","black";
8,"grad","white";
2,"theodore","green";
7,"teo","black";
8,"han","white";
3,"xsa","red";
2,"han","black";
6,"bob","green"}

where X, Y, and Color are labels. Lets assume that this data area is
subjected to AutoFilter by applying a criterion to X and/or Y.

Sheet2 (Destination sheet)

Create a distinct list of colors from the Color column in the data area on
Sheet1 using Advanced Filter.

Let A3:A7 house the following distinct list of colors:

{"Color";"black";"white";"green";"red"}

where Color is a label from the data area.

In B3 enter: Freq (which is just a label.)

In B4 enter & copy down:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!$C$3:$C$18,ROW(Sheet1!$C$3:$C$18)-MIN(R
OW(Sheet1!$C$3:$C$18)),,1)),--(Sheet1!$C$3:$C$18=A4))

In C3 enter: Rank (which is just a label.)

In C4 enter & copy down:

=RANK(B4,$B$4:$B$7)+COUNTIF($B$4:B4,B4)-1

In D1 enter: 3 (a Top N parameter value, with N = 3, picked up from your
problem description.)

In D2 type:

=MAX(IF(INDEX(B4:B7,MATCH(D1,C4:C7,0))=B4:B7,C4:C7))-D1

and confirm this with control+shift+enter instead of just with enter.

In D3 enter: Top 3 (which is just a label.)

In D4 enter & copy down:

=IF(ROW()-ROW(D$4)+1<=$D$1+$D$2,INDEX($A$4:$A$7,MATCH(ROW()-ROW(D$4)+1,$C$4:
$C$7,0)),"")

Underneth of the Top 3, you have a color result list that will change
automatically when you change the criteria of AutoFilter on Sheet1.