Comparing 2 sets of data ...help!

  • Thread starter Thread starter dancingbear72
  • Start date Start date
D

dancingbear72

Hi

I am very new to excel so if someone could explain how to get aroun
this problem simply I'd be eternaly grateful.

I know there are mamny threads on this sort of thing. I have done
search but there doesn't seem to be anything specific to this problem.

Here goes:

List 1: I have a list of names in a database (about 10,000)
List 2: I have a new list of names that I need to add to the database.

The thing is I need to filter the names to remove duplicates, but wha
I really need to do is know which of the names in list 2 are "ne
names", i.e. not in list 1.

I hope I'm explaining this right, basically I need a 3rd list of name
that are in list 2 but not in list 1, then this should give me a lis
of new names.

I have looked here http://www.cpearson.com/excel/duplicat.htm but it'
a bit complicated for me. Could anyone help ...please!!

Thanks in advance
Ro
 
Rob,

Use an empty column next to list 2, to use function VLookup in to identify
the wntries that already exist in list 1. The arguments should be:
Lookup_Value: The cell in the same row of list 2 containing the name
Table_Array: the range in list 1 containing the names
Col_Index_Num: 1 (this means it looks in the first column in the table
array)
Range_Lookup: False (so it looks for exact matches, ignoring near matches)

When you select the Table_Array range press F4 to make it an absolute
reference, so it stays constant when you then copy down. An absolute range
address should look like $A$1:$A$800 as opposed to the default relative one
which looks like A1:A800.
When you copy down, the matches will contain the name in the VLookup column,
while the unmatched names will return #N/A; the latter are the new ones, so
filter and copy to the bottom of the main list.

HTH,
Nikos
 
copy list1 at the end of list2
let us call this list3
give a heading to this list3
click data(menu)-filter-advancefilter
in advancefilter window against <lsit range> click the icon at the
righthand end
and highlight the complete list3 inlcluding the heading
dont do anything to criterarange
check <copy to another location> at the top
in <copy to >line again click the icon on theright hand side
choose some cell outside list 3
check unique record only at the bottom
click ok
you get the unique records only in the new location.
 
Back
Top