Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 -- 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
De-Duping
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" a écrit dans le message de news: ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
De-Duping
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
De-Duping
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...... -- strobinson1 ------------------------------------------------------------------------ strobinson1's Profile: http://www.excelforum.com/member.php...o&userid=36124 View this thread: http://www.excelforum.com/showthread...hreadid=559220 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
De-Duping
Thanks for this, I really appreciate it. -- 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
De-Duping
Why not just sort the listing on:
1) Company Name (Field) - Ascending 2) County Name (Field) - Ascending Just a thought.. "Mike JM" wrote in message : 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|