Excel 2007, Sort data by no. similar entries?

  • Thread starter Thread starter Andrew Staley
  • Start date Start date
A

Andrew Staley

Is it possible with Excel 2007 to sort data by the number of similar entries
within a column?

I.E

Row1 Row2
1977 Andrew
1980 Andrew
1960 Rachel
1950 Rachel
1940 Rachel
1965 John

What I want to be able to do is sort data in Row2 so that Rachel shows 1st
as she has 3 entries, then Andrew and finally John as he only has a single
instance, is this possible?

Thanks, Andrew.
 
How about using a helper column? I read your row1 as column1 and row2 as
column2, and put the formula like "=COUNTIF(B$1:B$6,B1)" into C1 and
copy it down to C6. then , sort the table by column3(first key) and
column1(second key).

Keiji
 
Is it possible with Excel 2007 to sort data by the number of similar entries
within a column?

I.E

Row1 Row2
1977 Andrew
1980 Andrew
1960 Rachel
1950 Rachel
1940 Rachel
1965 John

What I want to be able to do is sort data in Row2 so that Rachel shows 1st
as she has 3 entries, then Andrew and finally John as he only has a single
instance, is this possible?

Thanks, Andrew.
Click to expand...

Add a 3rd column. I guess in your context you would label it "Row3".

If your original table is in A1:Bn, then

C2: =COUNTIF($B$2:$B$7,B2)

Fill down to Cn.

Data/Sort
First by Row3 "Descending"
then (perhaps) by Row1 "Ascending"

Result:

1940 Rachel
1950 Rachel
1960 Rachel
1977 Andrew
1980 Andrew
1965 John
--ron
 
This data is really in columns, right?

If yes, then you could add a helper column (column C??) and use a formula like:

=countif(b:b,b1)
Then drag down as far as you have data

Then sort your data by this field.
 
Back
Top