De-Duping

[Mike JM]

Hi

I wonder if somebody could help me, I have a list of about 50,00
companies in Excel, which is split over 10 countries, I want to fin
companies that appear more than once in the list, therefore finding ou
who operates in more than one company.

Could anybody tell me how to do this?

Any information would be fantastic.

Many thanks

Mik

 

[Ardus Petus]

Hi,

Say your company list is in A1:A5000
In B1, enter:
=COUNTIF(A:A,A1)
and drag down.
This will give ne numnber of occurences of each company in your list

HTH

 

[Bondi]

Hi Mike,


To find the number of times a company appeares in the list you could
use something like:

=COUNTIF($A$1:$A$10,A1)

Where the company name is in column A and then just copy down the
formula.

Regards,
Bondi

 

[strobinson1]

I have done something similar to this.

Did it in two steps....

Assuming you list is in range (A2:A50001) allowing for headers

in B2: =countif($A$2:$A$50001,A2)

This will count the total entries of that company in the list. Copy it
down the list, then use a filter to only show rows with a value in
column B greater than 1.

This will show all the companies that have multiple entries, but will
also still have multiple entries for those companies


Next,
copy the filtered list in column A to another sheet.

Now using the "Advanced Filter" on your new list, select the Action
"Copy to Another Location", put in a new cell reference for your final
list to appear in the "Copy to:" option, and check the "Unique records
only" box.

Hit OK and you should be left with a list of the companies that appear
more than once from your original list.

A bit long winded to write, but takes only about 30secs to do !!

If you want to know how many times they appear, just do VLOOKUP against
your first list with column B returning the results......

 

[Mike JM]

Thanks for this, I really appreciate it

 

[Jim May]

Why not just sort the listing on:
1) Company Name (Field) - Ascending
2) County Name (Field) - Ascending
Just a thought..