Extract "Does not equal" data

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

Good morning –

Have searched this and can’t quite figure it out.

I have a column containing 1122 numbers. They are unique numbers.

I have another column that contains 359 of those 1122 numbers. How can I
determine which are the remaining 763 numbers. In other words, “ not equal
to†the 359 numbers.

Auto filter won’t work; I can’t copy in the 359 numbers after using the
“Does Not Equal†filter in the Custom box from the Drop-down Autofilter menu.

I am stumped! Thanks to anyone who takes the time to answer. I’ll keep a
close eye on this post for responses.

sam
 
Your 1122 are in a1:A1122
The 359 are in D1:D359
In B1 enter =IF(ISNA(LOOKUP(A1,$D$1:$D$359)),"X","")
Copy down column
All number that are not in the short list get an X in B
Use Edit | Paste Special -> Value on B
Sort A: B using B as the sort key
Of filter on B
best wishes
 
Oops, I was to quick for my own good!
Use =IF(ISNA(VLOOKUP(A1,$D$1:$D$9,1,FALSE)),"X","")
 
OR......copy the 359 and paste them below 1122...keep a row's gap so that
you know where they start from because you will delete them later. now
select the column go to Filter>Advance Filter>check COPY TO ANOTHER LOCATION
box and check UNIQUE RECORDS ONLY box. choose your range. Once you have the
unique ones...delete the ones you pasted in the first step.

does it help?
 
Oops, I was to quick for my own good!
Use =IF(ISNA(VLOOKUP(A1,$D$1:$D$9,1,FALSE)),"X","")
Click to expand...

Suggestion: wouldn't the unmatched items be produced more directly in
Col.B by just changing the "x" to A1 in the 2nd formula as follows:
Use=IF(ISNA(VLOOKUP(A1,$D1$:$D$9,1,FALSE)),A1,"")
.....AJ
 
Back
Top