Home |
Search |
Today's Posts |
#1
|
|||
|
|||
comparing lists of data to remove duplicate data
I have three lists on separate worksheets (i can combine them if necessary).
I want to compare List B and C to A (i.e. A is the control). Goal is to identify items in list B and C that are not on list A. I currently use MS Access to count the data and then manually filter the data (looking for items that had counts greater than 1 and manually checking each list). I've briefly looked at some list anaylsis functions in Excel, but they seem to assume that there is a known value to look for in the lists. I want to compare all the items in the list, which can be sorted in any order Question: is there an easier way to compare two or more lists and then to regenerate a new list without duplicates? (e.g. compare B to A and recreating B without items that were in A, B-A=B' ) |
#2
|
|||
|
|||
Chip Pearson has lots of ways to work with duplicates/uniques at:
http://www.cpearson.com/excel/duplicat.htm Tom wrote: I have three lists on separate worksheets (i can combine them if necessary). I want to compare List B and C to A (i.e. A is the control). Goal is to identify items in list B and C that are not on list A. I currently use MS Access to count the data and then manually filter the data (looking for items that had counts greater than 1 and manually checking each list). I've briefly looked at some list anaylsis functions in Excel, but they seem to assume that there is a known value to look for in the lists. I want to compare all the items in the list, which can be sorted in any order Question: is there an easier way to compare two or more lists and then to regenerate a new list without duplicates? (e.g. compare B to A and recreating B without items that were in A, B-A=B' ) -- Dave Peterson |
#3
|
|||
|
|||
I have been struggling with this same type of scenario on and off for a few
months. I finally have the ultimate solution for dealing with two lists that have matches and also potentially many differences. Insert a column to the right of the first set of data and fill it down with the number 1. Insert a column to the right of the second set of data and fill it down with -1. Copy both sets of data into a new worksheet and append the sets of data so that the 1's and -1's all line up with one another. Label the columns, calling the column that contains the 1's and -1's "Count". Now let the Pivot Table magic begin. Using the standard Pivot Table wizard to create a Pivot table in some cells further to the right of where of your sets of data have been pasted, just specify your entire range of data including the Count column. Drag and Drop "Names" into the "Drop Row Fields Here" space on the Pivot Table, then Drag and Drop "Count" into the "Drop Data Items Here" space on the Pivot Table. What you end up with is 1's next to those that are in the first list, but not in the second, 0's next to those that are in both lists, and -1's next to those that are in the second list, but not in the first! You have now identified all the differences between the two lists quite easily! "Dave Peterson" wrote: Chip Pearson has lots of ways to work with duplicates/uniques at: http://www.cpearson.com/excel/duplicat.htm Tom wrote: I have three lists on separate worksheets (i can combine them if necessary). I want to compare List B and C to A (i.e. A is the control). Goal is to identify items in list B and C that are not on list A. I currently use MS Access to count the data and then manually filter the data (looking for items that had counts greater than 1 and manually checking each list). I've briefly looked at some list anaylsis functions in Excel, but they seem to assume that there is a known value to look for in the lists. I want to compare all the items in the list, which can be sorted in any order Question: is there an easier way to compare two or more lists and then to regenerate a new list without duplicates? (e.g. compare B to A and recreating B without items that were in A, B-A=B' ) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have a cell return one of severl lists based on data from another | Excel Discussion (Misc queries) | |||
How can I automatically remove duplicate data within a column? | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Displaying, manipulating and printing huge lists of data | Excel Worksheet Functions | |||
Data Validation Lists | Links and Linking in Excel |