Idea how to match 500 ids to a list of 30k

[jrb]

I have list of 500 ids of sales people on one sheet and a list of 30
ids' on another. What I need to do is compare all 500 to the 30k t
find out if they exist and which group they belong to. I have bee
playing with EXACT and am failing. Does anyone have another suggestio
I might try?

Thanks

jr

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon

 

[Ken Wright]

Assuming your 500 IDs are on Sheet 1 in A1:A500, and 30K are on Sheet 2 in
A1:A30000, then on sheet1 in say D1 you can put:-

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$A$3000,1,0)),"",VLOOKUP(A1,Sheet2!$A$1:$A$30
00,1,0))

This will replicate that ID if it is to be found in the other list. If you have
your Group ID in say column B on sheet 2, and would rather return that, then
amend to:-

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$3000,2,0)),"",VLOOKUP(A1,Sheet2!$A$1:$B$30
00,2,0))

If that doesn't quite do what you want, then post back with more details

 

[Guest]

I Think its simple
insert 1 column on each sheet, name it as My Sales peopl
and another column in other sheet as "Which group
Then club both data into one and sort by email id,
Everything gets consoildated
Use if formula =if(d2=d1,"Got one",""

All the best