How to find top five States?

[Guest]

hi,
I have a problem in my records sheet. I have a field named "STATES", and the
task is to find out the top five states in it. Like the most repeated states
upto 5 levels. the Sample data is like,

MO
LA
LA
LA
MO
MO
MN
IL
FL
IA
MA
AK

Can anyone help me out to get rid of this problem. I am doing this manually
which is so difficult for me.

Thanks,
Saadi

 

[Bernie Deitrick]

Saadi,

First, you need to make a table of all the possible values for that column.
You can use Data | Filter... Advanced Filter, unique records, copy to
another location to do that. Then, next to those values, use a formula like

=COUNTIF(A:A, T2)

Where A is the column with your states, and T2 is the first cell of the new
table of possible values.

Then next to that formula, use a ranking formula like

=RANK(U2,$U$2:$U$51)

And copy down. Then filter on values less than 5, based on that formula.

HTH,
Bernie
MS Excel MVP

 

[Domenic]

Try the following approach, which will take into consideration ties for
5th place...

Assumption:

1) Column A contains your list

2) Your data starts in the second row

Formulas:

B2, copied down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),COUNTIF($A$2:$A$13,A2),"")

C2, copied down:

D1: enter 5 (indicating you want a Top 5 list)

E1:

=MAX(IF(B2:B13=INDEX(B2:B13,MATCH(D1,C2:C13,0)),C2:C13))-D1

....confirmed with CONTROL+SHIFT+ENTER

F2, copied down:

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

If you want a Top 10 list, Top 20 list, etc., change the number in D1 to
the desired number.

Hope this helps!

 

[Guest]

Thanks Bernie Deitrick !!!
Thanks for you kind help. But I did it with other way, for you and others
here it is, may be helpful for someone,

First I get all of the possible states name in Column AB, and then put
formula
=COUNTIF($D$4:$D$250,AB4)
in column AA, drag it down and I get the total number of existence of each
state.

Then I put
=LARGE($AA$4:$AA$39,1) for 1st Largest number
=LARGE($AA$4:$AA$39,2) for 2nd Largest number
=LARGE($AA$4:$AA$39,3) for 3rd Largest number
=LARGE($AA$4:$AA$39,4) for 4th Largest number
=LARGE($AA$4:$AA$39,5) for 5th Largest number

in Cells D300, D301, D302, D303, D304, D305.

Then I put
=VLOOKUP(D300,$AA$4:$AB$39,2,FALSE)
and drag it down for all of five cells. and got me required result.

I know it is so confusing, but it good to get something by your own self.

once again Thanks Man,

Saadi

 

[Guest]

Thanks Bernie Deitrick !!!
Thanks for you kind help. But I did it with other way, for you and others
here it is, may be helpful for someone,

First I get all of the possible states name in Column AB, and then put
formula
=COUNTIF($D$4:$D$250,AB4)
in column AA, drag it down and I get the total number of existence of each
state.

Then I put
=LARGE($AA$4:$AA$39,1) for 1st Largest number
=LARGE($AA$4:$AA$39,2) for 2nd Largest number
=LARGE($AA$4:$AA$39,3) for 3rd Largest number
=LARGE($AA$4:$AA$39,4) for 4th Largest number
=LARGE($AA$4:$AA$39,5) for 5th Largest number

in Cells D300, D301, D302, D303, D304, D305.

Then I put
=VLOOKUP(D300,$AA$4:$AB$39,2,FALSE)
and drag it down for all of five cells. and got me required result.

I know it is so confusing, but it good to get something by your own self.

once again Thanks Man,

Saadi

 

[Guest]

Domenic,

I have tried but can't do it. can you please make it in a Excel sheet and
send it over. my mail id is (e-mail address removed)

I shall be very thankful to you for this favor.

Saadi

 

[Domenic]

I just now emailed you a copy. I hope you find it helpful.

Cheers!

 

[Aladin Akyurek]

Didn't you ask all this before in

http://tinyurl.com/4xefs ?