De-Duping

Hi

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

Could anybody tell me how to do this?

Any information would be fantastic.

Many thanks

Mike

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
--
AP

"Mike JM" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi
>
> I wonder if somebody could help me, I have a list of about 50,000
> companies in Excel, which is split over 10 countries, I want to find
> companies that appear more than once in the list, therefore finding out
> who operates in more than one company.
>
> Could anybody tell me how to do this?
>
> Any information would be fantastic.
>
> Many thanks
>
> Mike
>
>
> --
> Mike JM
> ------------------------------------------------------------------------
> Mike JM's Profile:
> http://www.excelforum.com/member.php...o&userid=36148
> View this thread: http://www.excelforum.com/showthread...hreadid=559220
>

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

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......

Thanks for this, I really appreciate it.

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

"Mike JM" <[email protected]> wrote
in message news:[email protected]:

> Hi
>
> I wonder if somebody could help me, I have a list of about 50,000
> companies in Excel, which is split over 10 countries, I want to find
> companies that appear more than once in the list, therefore finding out
> who operates in more than one company.
>
> Could anybody tell me how to do this?
>
> Any information would be fantastic.
>
> Many thanks
>
> Mike
>
>
> --
> Mike JM
> ------------------------------------------------------------------------
> Mike JM's Profile: http://www.excelforum.com/member.php...o&userid=36148
> View this thread: http://www.excelforum.com/showthread...hreadid=559220