Okay so far so good
Now I have the "long" data in sheet 01.0A and the "short" data in
sheet 01.0B used basically the same SUMPRODUCT formula in sheet 01.0C

and used

=IF(ROWS($B$1:B1)<$A$1,INDEX('01.0A'!$A$1:$A$6,SMA LL(IF(COUNTIF('01.0B'!$A$1:$A$2,'01.0A'!$A$1:$A$6) =0,ROW('01.0A'!$A$1:$A$6)-ROW('01.0A'!$A$1)+1),ROWS($B$1:B1))),"")

This works allright, but it only lists the id # for each record, I want all
the data for the whole record (column A to column D) what do I need to
change?

Here is an example, if want

W3245 peach 358 red
W2178 lemmon 548 yellow

but I only get

W3245
W2178

I know I could concatanate all the data in each record so it fits into just
one cell, but is there an easier way?

Les

"Peo Sjoblom" wrote in message
...
It works fine for me as expected
you can change Dominic's formula to fit that as well but it would be
easier if you put

a,b,c,d,e,f, in A1:A5 and a,d,f, in B1:B3 then change Dominic's formula in
C1 to

=SUMPRODUCT(--(COUNTIF($B$1:$B$3,$A$1:$A$6)=0))

and the formula in D1 to

=IF(ROWS($D$1:D1)<=$C$1,INDEX($A$1:$A$6,SMALL(IF(C OUNTIF($B$1:$B$3,$A$1:$A$6)=0,ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($D$1:D1))),"")

array enter and copy down returns b, c and e

if you want to keep your layout change Dominic's formula accordingly

--
Regards,

Peo Sjoblom

(No private emails please)


"Leslie Coover" wrote in message
news:_xWJe.327$U92.24@okepread06...
When I used your formulas a 3 was generated in cell C1 and zeros in D1:D3

Here is the data
A1:A3 {a, d, f}
B1:B6 {a, b, c, d, e, f}

I want the items in B1:B6 that don't appear in A1:A3

Tried to use a Vlookup and If function and it worked but was cumbersome.

How about this VBA code solution?

Create an outside loop that steps through each item in the long list.
Create an inside loop that steps through each item on the short list

If there is no match the active cell is printed in an adjacent cell. If
there is a match
the inside loop terminates and the outside loop increments to the next
item on the list.

And so it goes until the last item on the long list is checked.

Could someone give me some help with this code?

Les

"Domenic" wrote in message
...
Assuming that Column A contains your first list, and Column B contains
your second list, try...

C1:

=SUMPRODUCT(--(COUNTIF($B$1:$B$4,$A$1:$A$7)=0))

D1, copied down:

=IF(ROWS($D$1:D1)<=$C$1,INDEX($A$1:$A$7,SMALL(IF(C OUNTIF($B$1:$B$4,$A$1:$
A$7)=0,ROW($A$1:$A$7)-ROW($A$1)+1),ROWS($D$1:D1))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <byUJe.308$U92.51@okepread06,
"Leslie Coover" wrote:

Suppose you have two lists:

a, b, c, d, e, f, g

and
a, b, e, g

and you want to extract only the items on the first list that are not
on
the second list.

I tried =IF(A1<$B1:$B8,A1)

and also

{=IF(A1<$B1:$B8,A1)}

neither worked, any suggestions?

Thanks,
Les