comparing lists of data to remove duplicate data

[Tom]

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]

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

[GeorgiaW]

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
>