create new list from list A, but with exclusions from a list B

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

Harold Good

Hi,

I'd like to create a new list from List A which might have up to 50 unique
numbers, but that may change as data changes. But there is a List B into
which a user can input some numbers from List A that he doesn't want to
appear in New List.
In the example below, New List is my goal. What formula can I use in New
List cells to calculate these numbers in New List?
Sorry these columns probably won't line up.

List A List B New List
272 992 272
68 111 68
111 14 260
260 299
299 233
233 261
14
261
992
 
List A/B assumed in cols A and B, with data from row2 down

In C2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW()))
Leave C1 empty

In D2:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1))))
Select C2:D2, copy down to cover the max expected extent of data in col A,
say, down to D200? Minimize/hide away col C. Col D will return exactly the
results that you seek.
 
Max, that worked perfectly! Thanks so much for your kind help.
BTW, last summer I visited your city and really enjoyed it!

Harold
 
Back
Top