Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to delete duplicate records when I merge two lists (deleting .
I am merging two mailing lists (lists A & B). Some of the names from list A
are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#2
|
|||
|
|||
You can use a formula like this
=COUNTIF(A$1:A$1000,A1) next to all your records. Then you can sort them by that column and delete anything with a "2" next to it. More info here; http://www.cpearson.com/excel/duplicat.htm "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#3
|
|||
|
|||
Hi
My Add-in have a duplicate option that you can use http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#4
|
|||
|
|||
That is a great tool, but can it be used when I am searching for duplicates
in data across multiple columns? Example: Last Name First Name Address Smith John 123 Main Street "Ron de Bruin" wrote: Hi My Add-in have a duplicate option that you can use http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#5
|
|||
|
|||
How would I apply this to identify duplicates within data that spans multiple
columns? Example: Last Name First Name Address Smith John 123 Main St Adams Bill 456 Park St Smith John 123 Park St "Dave R." wrote: You can use a formula like this =COUNTIF(A$1:A$1000,A1) next to all your records. Then you can sort them by that column and delete anything with a "2" next to it. More info here; http://www.cpearson.com/excel/duplicat.htm "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#6
|
|||
|
|||
in data across multiple columns?
No this version one column But you can use a helper column that will merge your last and first name and use the duplicate option on that column =A1&" " & B1 -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" wrote in message ... That is a great tool, but can it be used when I am searching for duplicates in data across multiple columns? Example: Last Name First Name Address Smith John 123 Main Street "Ron de Bruin" wrote: Hi My Add-in have a duplicate option that you can use http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#7
|
|||
|
|||
Is it possible to merge more than 2 columns? Say 4 or 5 to include Street
Address, City, State, etc... "Ron de Bruin" wrote: in data across multiple columns? No this version one column But you can use a helper column that will merge your last and first name and use the duplicate option on that column =A1&" " & B1 -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" wrote in message ... That is a great tool, but can it be used when I am searching for duplicates in data across multiple columns? Example: Last Name First Name Address Smith John 123 Main Street "Ron de Bruin" wrote: Hi My Add-in have a duplicate option that you can use http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#8
|
|||
|
|||
Yes
You can change the formula Or use a function http://www.mcgimpsey.com/excel/mergedata.html -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" wrote in message ... Is it possible to merge more than 2 columns? Say 4 or 5 to include Street Address, City, State, etc... "Ron de Bruin" wrote: in data across multiple columns? No this version one column But you can use a helper column that will merge your last and first name and use the duplicate option on that column =A1&" " & B1 -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" wrote in message ... That is a great tool, but can it be used when I am searching for duplicates in data across multiple columns? Example: Last Name First Name Address Smith John 123 Main Street "Ron de Bruin" wrote: Hi My Add-in have a duplicate option that you can use http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#9
|
|||
|
|||
And this one
http://www.mcgimpsey.com/excel/udfs/multicat.html -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Yes You can change the formula Or use a function http://www.mcgimpsey.com/excel/mergedata.html -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" wrote in message ... Is it possible to merge more than 2 columns? Say 4 or 5 to include Street Address, City, State, etc... "Ron de Bruin" wrote: in data across multiple columns? No this version one column But you can use a helper column that will merge your last and first name and use the duplicate option on that column =A1&" " & B1 -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" wrote in message ... That is a great tool, but can it be used when I am searching for duplicates in data across multiple columns? Example: Last Name First Name Address Smith John 123 Main Street "Ron de Bruin" wrote: Hi My Add-in have a duplicate option that you can use http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#10
|
|||
|
|||
Hi
one way: - use a helper column. e.g. in d1: =A1 & "^" & B1 & "^" & C1 and uye this helper column -- Regards Frank Kabel Frankfurt, Germany rinks wrote: How would I apply this to identify duplicates within data that spans multiple columns? Example: Last Name First Name Address Smith John 123 Main St Adams Bill 456 Park St Smith John 123 Park St "Dave R." wrote: You can use a formula like this =COUNTIF(A$1:A$1000,A1) next to all your records. Then you can sort them by that column and delete anything with a "2" next to it. More info here; http://www.cpearson.com/excel/duplicat.htm "rinks" <rinks @discussions.microsoft.com wrote in message ... I am merging two mailing lists (lists A & B). Some of the names from list A are also in List B. When I merge the two, I want to delete any records that are duplicated. I want to delete the original record and the duplicate, so an advanced filter selecting "Unique records only" will not work. What do I do? Is there an "IF" statement that I can run against the merged list that will identify and deleted any record that is duplicated? |
#11
|
|||
|
|||
rinks wrote...
How would I apply this to identify duplicates within data that spans multiple columns? Example: Last Name First Name Address Smith John 123 Main St Adams Bill 456 Park St Smith John 123 Park St .... Many responses to how to do this after the fact. Better to avoid duplicates in the first place, and you *can* use advanced filters for that. If the first list with column headings were in the active worksheet in A1:C100 and the second list in XYZ!A1:C200, then copy the column headings to E1:G1 and enter the folllowing formula in I2. =SUMPRODUCT((A2=XYZ!$A$2:$A$200)*(B2=XYZ!$B$2:$B$2 00)*(C2=XYZ!$C$2:$C$200))=0 Now select the first list in A1:C100 and run the menu command Data Filter Advanced Filter... . Choose 'Copy to another location', select I1:I2 as the Criteria range and E1:G1 as the Copy to [range], check 'Unique records only', and click OK. This will put all records from the first list with no 3-field match in the second list into columns E:G with the first record in E2:G2. Then copy the column headings into the row immediately below the last extracted record in E:G, and run the menu command Data Filter Advanced Filter... . Choose 'Copy to another location', change the List range to XYZ!A1:C200, clear the entry for the Criteria range (don't use any criteria), set the Copy to [range] to the newly copied row of column headings in E:G, check 'Unique records only', and click OK. This will add the 3-column distinct records from the second list to the merged list in E:G. Finally, delete (as in Edit Delete, move cells up) the second row of column headings in E:G. What's left in E:G is the list without 3-column duplicates. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to delete rows with duplicate entries within one column. | Excel Discussion (Misc queries) | |||
Finding duplicate records in Excel | Excel Discussion (Misc queries) | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) | |||
Duplicate records in Excel | Excel Discussion (Misc queries) | |||
Add numbers for duplicate entries then delete | Excel Worksheet Functions |