put most duplicates at the top...

R

Rogelio

hey, I have a list of about 8500 people. some of them are duplicates for
sure.. many of them are.

what I want to do is re order the list so that the person with the most
duplicates is the first in the list (along with ALL of his duplicates) and
descend from there. know what I mean?

for example if the list was 100 people, and 30 are John, 20 are Jim and 50
are Mary it would re order the list so that

the first 50 records are mary,mary,mary all the way down,
the next 30 are john all the way down,
the last 20 are Jim

the total count remains the same. and I get to see who has the most
duplicates. I'm in a situation where I have 10 excel spreadsheets, and if
there is 10 of one guy, then he can go into each excel spreadsheet. if
someone only has 5, then he only goes into 5 different excel spreadsheets.

any help in VBA or formula will work. I have a BS in CS and I'm a software
developer.
 
J

Jim Thomlinson

I would be inclined to create a pivot table from the names list. Add the
names into the left hand column and also into the data area. This should give
you a count of all of the unique names. Now just change the Field Settings ->
Layout to sort the names by the count (Descending).
 
C

CurlerBob

I'd use the formula =countif($A$1:$A$8500,A1) and copy it all the way down.
Then sort by this column descending and then name. They'll all be in order
with the most at the top.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top