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

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

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

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,R OWS($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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Harold Good" wrote in message
...
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

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

Max, that worked perfectly! Thanks so much for your kind help.
BTW, last summer I visited your city and really enjoyed it!

Harold


"Max" wrote in message
...
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,R OWS($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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Harold Good" wrote in message
...
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

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

Delighted to hear that, Harold. On both counts.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Harold Good" wrote in message
...
Max, that worked perfectly! Thanks so much for your kind help.
BTW, last summer I visited your city and really enjoyed it!

Harold