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,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

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

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