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

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

Post by Harold Goo » Sat, 12 Apr 2008 05:58:55


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

Post by Max » Sat, 12 Apr 2008 16:54:26

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
Singapore
http://www.yqcomputer.com/
xdemechanik
---

 
 
 

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

Post by Harold Goo » Sat, 12 Apr 2008 23:19:43

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

Harold
 
 
 

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

Post by Max » Sun, 13 Apr 2008 07:23:20

Delighted to hear that, Harold. On both counts.
--
Max
Singapore
http://www.yqcomputer.com/
xdemechanik
---