Filter one sheet by others

[Dylan]

Hi all,
Thanks to your answer to my previous query here yesterday. For my next
trick, I need to be able to remove rows in order to provide a unique list.
Consider the scenario:

I was given a 3000-strong customer list ('a'), and asked to choose 900 at
random ('b') for a survey. This sample was given as a new excel file. All
fine, but the response rate was too low, so another 900 were chosen ('c').
Now, the response rate is still too low, and I have been asked to provide a
list of all the customers that have not been contacted ('d') - so that we
don't annoy people by calling them twice.

basically d = a - b - c ... sounds simple but i can't figure out a way!
Maybe you lot have a patently obvious solution?



Thanks

Dylan

 

[Arvi Laanemets]

Hi

Let you have list b in workbook first.xls in range Sheet1!A1:X900, and list
c in workbook second.xls in range Sheet1!A1:X900, with identifier in column
A.
In list a (identifier also in column A), add an additional column (Y) with
formula (on fly):
Y1=IF(OR(ISERROR(VLOOKUP(A1,'C:\..\[first.xls]Sheet1!'$A$1:$A$900,1,FALSE)),
ISERROR(VLOOKUP(A1,'C:\..\[Second.xls]Sheet1!'$A$1:$A$900,1,FALSE))),TRUE,FA
LSE)
Now use autofilter to get the rows with Y=TRUE

 

[BrianB]

Take your main list and add columns to perform lookups on your other
lists. Sort on these columns. #N/A against both means you haven'y used
those names.

Regards
BrianB
===================================================================