Extract only items not on previous list

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(COUNTIF($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" <[email protected]> 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

On Tue, 9 Aug 2005 23:28:35 -0500, "Leslie Coover" <[email protected]> wrote:

>Thanks Ron, this works good
>
>When there is an id field and an associated name field this method can
>extract only the records in list 1(columns A and B) that do not appear in
>list 2 (columns C and D) and then print the result in say columns G and H.
>This is a big help.
>Thanks,

Glad to help. Thank you for the feedback.
--ron

Make sure that this part of the formula...

INDEX('01.0A'!$A$1:$A$6

....is changed to...

INDEX('01.0A'!A$1:A$6

Therefore, your formulas should be as follows...

A1:

=SUMPRODUCT(--(COUNTIF('01.0B'!$A$1:$A$2,'01.0A'!$A$1:$A$6)=0))

B1, copied down and across:

=IF(ROWS(B$1:B1)<=$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),R
OWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <GGfKe.399$U92.87@okepread06>,
"Leslie Coover" <[email protected]> wrote:

> Thanks Domenic, I could not get the results I wanted, it simply displayed
> the same id numbers
> repeatedly, rather than including data in adjacent cells.
>
> If I had really huge data sets your method is definitely superior as results
> can be printed on one worksheet
> and the "long list" and "short list" can be kept in two other worksheets.
> I'm sure there is a way to extract data in adjacent cells along with primary
> key data, but the data sets I am concerned with are not that large so I
> decided to use Ron's filter method (in this thread). To use that method all
> the data must be kept on one sheet (there may be a way to use multiple
> sheets here too--but I do not know it). The advantage is that the extracted
> records can contain data in adjacent cells along with the primary data key.
>
> Les
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > Try...
> >
> > B1, copied down and across:
> >
> > =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> > WS(B$1:B1))),"")
> >
> > Note that the column reference for...
> >
> > INDEX('01.0A'!A$1:A$6
> >
> > ...has been changed to a relative reference.
> >
> > Hope this helps!
> >
> > In article <rf4Ke.353$U92.195@okepread06>,
> > "Leslie Coover" <[email protected]> wrote:
> >
> >> 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,SMALL(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

Thanks Domenic, I could not get the results I wanted, it simply displayed
the same id numbers
repeatedly, rather than including data in adjacent cells.

If I had really huge data sets your method is definitely superior as results
can be printed on one worksheet
and the "long list" and "short list" can be kept in two other worksheets.
I'm sure there is a way to extract data in adjacent cells along with primary
key data, but the data sets I am concerned with are not that large so I
decided to use Ron's filter method (in this thread). To use that method all
the data must be kept on one sheet (there may be a way to use multiple
sheets here too--but I do not know it). The advantage is that the extracted
records can contain data in adjacent cells along with the primary data key.

Les
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> B1, copied down and across:
>
> =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> WS(B$1:B1))),"")
>
> Note that the column reference for...
>
> INDEX('01.0A'!A$1:A$6
>
> ...has been changed to a relative reference.
>
> Hope this helps!
>
> In article <rf4Ke.353$U92.195@okepread06>,
> "Leslie Coover" <[email protected]> wrote:
>
>> 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,SMALL(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

Thanks Ron, this works good

When there is an id field and an associated name field this method can
extract only the records in list 1(columns A and B) that do not appear in
list 2 (columns C and D) and then print the result in say columns G and H.
This is a big help.
Thanks,

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]>
> wrote:
>
>>Not sure what you mean?
>>
>>A1:A6 = {a, b, c, d, e, f}
>>B1:B3 = {a, d, f}
>>
>>how should I reorganize the data to use the formula
>>=COUNTIF($A$9:$A$11,B9)=0
>>as a criteria in an advance filter and what should I enter in
>>List Range box?
>>
>>Thanks.
>
> OK , you've reversed your lists. And you do have to have things laid out
> in a
> defined way to get the advanced filter to work. Here's one way.
>
> I assume that the result you want is {b,c,e}.
>
> Set up a worksheet as follows
>
> A B
> List1 List2
> a a
> b d
> c f
> d
> e
> f
>
> (In columns A & B; and Rows 1-7; note that a header row is used)
>
> E1: <empty>
> E2: =COUNTIF($B$2:$B$4,A2)=0
>
> Data/Filter/Advanced Filter
> Action: Copy to another location
> List Range: $A$1:$B$7
> Criteria Range: $E$1:$E$2
> Copy to: G1
> <OK>
>
> In columns G & H you will see:
>
> List1 List2
> b d
> c f
> e
>
> You are only interested in List1 so you can either delete H1:H3 or copy
> column
> G to wherever. Or set things up to display the results some other way.
>
> The above could also be done using a macro. Probably you should use the
> macro
> recorder if you choose this option.
>
>
>
> --ron

On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]> wrote:

>Not sure what you mean?
>
>A1:A6 = {a, b, c, d, e, f}
>B1:B3 = {a, d, f}
>
>how should I reorganize the data to use the formula
>=COUNTIF($A$9:$A$11,B9)=0
>as a criteria in an advance filter and what should I enter in
>List Range box?
>
>Thanks.

OK , you've reversed your lists. And you do have to have things laid out in a
defined way to get the advanced filter to work. Here's one way.

I assume that the result you want is {b,c,e}.

Set up a worksheet as follows

A B
List1 List2
a a
b d
c f
d
e
f

(In columns A & B; and Rows 1-7; note that a header row is used)

E1: <empty>
E2: =COUNTIF($B$2:$B$4,A2)=0

Data/Filter/Advanced Filter
Action: Copy to another location
List Range: $A$1:$B$7
Criteria Range: $E$1:$E$2
Copy to: G1
<OK>

In columns G & H you will see:

List1 List2
b d
c f
e

You are only interested in List1 so you can either delete H1:H3 or copy column
G to wherever. Or set things up to display the results some other way.

The above could also be done using a macro. Probably you should use the macro
recorder if you choose this option.



--ron

Try...

B1, copied down and across:

=IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
WS(B$1:B1))),"")

Note that the column reference for...

INDEX('01.0A'!A$1:A$6

....has been changed to a relative reference.

Hope this helps!

In article <rf4Ke.353$U92.195@okepread06>,
"Leslie Coover" <[email protected]> wrote:

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

Not sure what you mean?

A1:A6 = {a, b, c, d, e, f}
B1:B3 = {a, d, f}

how should I reorganize the data to use the formula
=COUNTIF($A$9:$A$11,B9)=0
as a criteria in an advance filter and what should I enter in
List Range box?

Thanks.

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]>
> 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
>>
>
> You could also use the Advanced Filter (On the Data menu).
>
> If your data was moved down to A9, then the formula would be:
>
> =COUNTIF($A$9:$A$11,B9)=0
>
>
> --ron

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,SMALL(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" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]> 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
>

You could also use the Advanced Filter (On the Data menu).

If your data was moved down to A9, then the formula would be:

=COUNTIF($A$9:$A$11,B9)=0


--ron

Works fine, thanks!

Les

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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(COUNTIF($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" <[email protected]> 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

Hi!

The formula works but the example you posted originally is the opposite of
the example you just posted. So, just reverse some of the references:

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

Array entered.

Biff

"Leslie Coover" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

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,SMALL(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" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

Not sure what you mean?

A1:A6 = {a, b, c, d, e, f}
B1:B3 = {a, d, f}

how should I reorganize the data to use the formula
=COUNTIF($A$9:$A$11,B9)=0
as a criteria in an advance filter and what should I enter in
List Range box?

Thanks.

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]>
> 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
>>
>
> You could also use the Advanced Filter (On the Data menu).
>
> If your data was moved down to A9, then the formula would be:
>
> =COUNTIF($A$9:$A$11,B9)=0
>
>
> --ron

Try...

B1, copied down and across:

=IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
WS(B$1:B1))),"")

Note that the column reference for...

INDEX('01.0A'!A$1:A$6

....has been changed to a relative reference.

Hope this helps!

In article <rf4Ke.353$U92.195@okepread06>,
"Leslie Coover" <[email protected]> wrote:

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

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(COUNTIF($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" <[email protected]> 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

On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]> wrote:

>Not sure what you mean?
>
>A1:A6 = {a, b, c, d, e, f}
>B1:B3 = {a, d, f}
>
>how should I reorganize the data to use the formula
>=COUNTIF($A$9:$A$11,B9)=0
>as a criteria in an advance filter and what should I enter in
>List Range box?
>
>Thanks.

OK , you've reversed your lists. And you do have to have things laid out in a
defined way to get the advanced filter to work. Here's one way.

I assume that the result you want is {b,c,e}.

Set up a worksheet as follows

A B
List1 List2
a a
b d
c f
d
e
f

(In columns A & B; and Rows 1-7; note that a header row is used)

E1: <empty>
E2: =COUNTIF($B$2:$B$4,A2)=0

Data/Filter/Advanced Filter
Action: Copy to another location
List Range: $A$1:$B$7
Criteria Range: $E$1:$E$2
Copy to: G1
<OK>

In columns G & H you will see:

List1 List2
b d
c f
e

You are only interested in List1 so you can either delete H1:H3 or copy column
G to wherever. Or set things up to display the results some other way.

The above could also be done using a macro. Probably you should use the macro
recorder if you choose this option.



--ron

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" <[email protected]> wrote in message
news:[email protected]...
> 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(COUNTIF($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" <[email protected]> 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

Hi!

The formula works but the example you posted originally is the opposite of
the example you just posted. So, just reverse some of the references:

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

Array entered.

Biff

"Leslie Coover" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

Thanks Ron, this works good

When there is an id field and an associated name field this method can
extract only the records in list 1(columns A and B) that do not appear in
list 2 (columns C and D) and then print the result in say columns G and H.
This is a big help.
Thanks,

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]>
> wrote:
>
>>Not sure what you mean?
>>
>>A1:A6 = {a, b, c, d, e, f}
>>B1:B3 = {a, d, f}
>>
>>how should I reorganize the data to use the formula
>>=COUNTIF($A$9:$A$11,B9)=0
>>as a criteria in an advance filter and what should I enter in
>>List Range box?
>>
>>Thanks.
>
> OK , you've reversed your lists. And you do have to have things laid out
> in a
> defined way to get the advanced filter to work. Here's one way.
>
> I assume that the result you want is {b,c,e}.
>
> Set up a worksheet as follows
>
> A B
> List1 List2
> a a
> b d
> c f
> d
> e
> f
>
> (In columns A & B; and Rows 1-7; note that a header row is used)
>
> E1: <empty>
> E2: =COUNTIF($B$2:$B$4,A2)=0
>
> Data/Filter/Advanced Filter
> Action: Copy to another location
> List Range: $A$1:$B$7
> Criteria Range: $E$1:$E$2
> Copy to: G1
> <OK>
>
> In columns G & H you will see:
>
> List1 List2
> b d
> c f
> e
>
> You are only interested in List1 so you can either delete H1:H3 or copy
> column
> G to wherever. Or set things up to display the results some other way.
>
> The above could also be done using a macro. Probably you should use the
> macro
> recorder if you choose this option.
>
>
>
> --ron

Works fine, thanks!

Les

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

Thanks Domenic, I could not get the results I wanted, it simply displayed
the same id numbers
repeatedly, rather than including data in adjacent cells.

If I had really huge data sets your method is definitely superior as results
can be printed on one worksheet
and the "long list" and "short list" can be kept in two other worksheets.
I'm sure there is a way to extract data in adjacent cells along with primary
key data, but the data sets I am concerned with are not that large so I
decided to use Ron's filter method (in this thread). To use that method all
the data must be kept on one sheet (there may be a way to use multiple
sheets here too--but I do not know it). The advantage is that the extracted
records can contain data in adjacent cells along with the primary data key.

Les
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> B1, copied down and across:
>
> =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> WS(B$1:B1))),"")
>
> Note that the column reference for...
>
> INDEX('01.0A'!A$1:A$6
>
> ...has been changed to a relative reference.
>
> Hope this helps!
>
> In article <rf4Ke.353$U92.195@okepread06>,
> "Leslie Coover" <[email protected]> wrote:
>
>> 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,SMALL(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

On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]> 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
>

You could also use the Advanced Filter (On the Data menu).

If your data was moved down to A9, then the formula would be:

=COUNTIF($A$9:$A$11,B9)=0


--ron

Make sure that this part of the formula...

INDEX('01.0A'!$A$1:$A$6

....is changed to...

INDEX('01.0A'!A$1:A$6

Therefore, your formulas should be as follows...

A1:

=SUMPRODUCT(--(COUNTIF('01.0B'!$A$1:$A$2,'01.0A'!$A$1:$A$6)=0))

B1, copied down and across:

=IF(ROWS(B$1:B1)<=$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),R
OWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <GGfKe.399$U92.87@okepread06>,
"Leslie Coover" <[email protected]> wrote:

> Thanks Domenic, I could not get the results I wanted, it simply displayed
> the same id numbers
> repeatedly, rather than including data in adjacent cells.
>
> If I had really huge data sets your method is definitely superior as results
> can be printed on one worksheet
> and the "long list" and "short list" can be kept in two other worksheets.
> I'm sure there is a way to extract data in adjacent cells along with primary
> key data, but the data sets I am concerned with are not that large so I
> decided to use Ron's filter method (in this thread). To use that method all
> the data must be kept on one sheet (there may be a way to use multiple
> sheets here too--but I do not know it). The advantage is that the extracted
> records can contain data in adjacent cells along with the primary data key.
>
> Les
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > Try...
> >
> > B1, copied down and across:
> >
> > =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> > WS(B$1:B1))),"")
> >
> > Note that the column reference for...
> >
> > INDEX('01.0A'!A$1:A$6
> >
> > ...has been changed to a relative reference.
> >
> > Hope this helps!
> >
> > In article <rf4Ke.353$U92.195@okepread06>,
> > "Leslie Coover" <[email protected]> wrote:
> >
> >> 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,SMALL(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

On Tue, 9 Aug 2005 23:28:35 -0500, "Leslie Coover" <[email protected]> wrote:

>Thanks Ron, this works good
>
>When there is an id field and an associated name field this method can
>extract only the records in list 1(columns A and B) that do not appear in
>list 2 (columns C and D) and then print the result in say columns G and H.
>This is a big help.
>Thanks,

Glad to help. Thank you for the feedback.
--ron

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(COUNTIF($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" <[email protected]> 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

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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

On Tue, 9 Aug 2005 23:28:35 -0500, "Leslie Coover" <[email protected]> wrote:

>Thanks Ron, this works good
>
>When there is an id field and an associated name field this method can
>extract only the records in list 1(columns A and B) that do not appear in
>list 2 (columns C and D) and then print the result in say columns G and H.
>This is a big help.
>Thanks,

Glad to help. Thank you for the feedback.
--ron

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" <[email protected]> wrote in message
news:[email protected]...
> 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(COUNTIF($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" <[email protected]> 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

Hi!

The formula works but the example you posted originally is the opposite of
the example you just posted. So, just reverse some of the references:

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

Array entered.

Biff

"Leslie Coover" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

Thanks Domenic, I could not get the results I wanted, it simply displayed
the same id numbers
repeatedly, rather than including data in adjacent cells.

If I had really huge data sets your method is definitely superior as results
can be printed on one worksheet
and the "long list" and "short list" can be kept in two other worksheets.
I'm sure there is a way to extract data in adjacent cells along with primary
key data, but the data sets I am concerned with are not that large so I
decided to use Ron's filter method (in this thread). To use that method all
the data must be kept on one sheet (there may be a way to use multiple
sheets here too--but I do not know it). The advantage is that the extracted
records can contain data in adjacent cells along with the primary data key.

Les
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> B1, copied down and across:
>
> =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> WS(B$1:B1))),"")
>
> Note that the column reference for...
>
> INDEX('01.0A'!A$1:A$6
>
> ...has been changed to a relative reference.
>
> Hope this helps!
>
> In article <rf4Ke.353$U92.195@okepread06>,
> "Leslie Coover" <[email protected]> wrote:
>
>> 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,SMALL(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

Works fine, thanks!

Les

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]> 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
>

You could also use the Advanced Filter (On the Data menu).

If your data was moved down to A9, then the formula would be:

=COUNTIF($A$9:$A$11,B9)=0


--ron

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,SMALL(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" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

Not sure what you mean?

A1:A6 = {a, b, c, d, e, f}
B1:B3 = {a, d, f}

how should I reorganize the data to use the formula
=COUNTIF($A$9:$A$11,B9)=0
as a criteria in an advance filter and what should I enter in
List Range box?

Thanks.

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]>
> 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
>>
>
> You could also use the Advanced Filter (On the Data menu).
>
> If your data was moved down to A9, then the formula would be:
>
> =COUNTIF($A$9:$A$11,B9)=0
>
>
> --ron

Try...

B1, copied down and across:

=IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
WS(B$1:B1))),"")

Note that the column reference for...

INDEX('01.0A'!A$1:A$6

....has been changed to a relative reference.

Hope this helps!

In article <rf4Ke.353$U92.195@okepread06>,
"Leslie Coover" <[email protected]> wrote:

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

Make sure that this part of the formula...

INDEX('01.0A'!$A$1:$A$6

....is changed to...

INDEX('01.0A'!A$1:A$6

Therefore, your formulas should be as follows...

A1:

=SUMPRODUCT(--(COUNTIF('01.0B'!$A$1:$A$2,'01.0A'!$A$1:$A$6)=0))

B1, copied down and across:

=IF(ROWS(B$1:B1)<=$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),R
OWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <GGfKe.399$U92.87@okepread06>,
"Leslie Coover" <[email protected]> wrote:

> Thanks Domenic, I could not get the results I wanted, it simply displayed
> the same id numbers
> repeatedly, rather than including data in adjacent cells.
>
> If I had really huge data sets your method is definitely superior as results
> can be printed on one worksheet
> and the "long list" and "short list" can be kept in two other worksheets.
> I'm sure there is a way to extract data in adjacent cells along with primary
> key data, but the data sets I am concerned with are not that large so I
> decided to use Ron's filter method (in this thread). To use that method all
> the data must be kept on one sheet (there may be a way to use multiple
> sheets here too--but I do not know it). The advantage is that the extracted
> records can contain data in adjacent cells along with the primary data key.
>
> Les
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > Try...
> >
> > B1, copied down and across:
> >
> > =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> > WS(B$1:B1))),"")
> >
> > Note that the column reference for...
> >
> > INDEX('01.0A'!A$1:A$6
> >
> > ...has been changed to a relative reference.
> >
> > Hope this helps!
> >
> > In article <rf4Ke.353$U92.195@okepread06>,
> > "Leslie Coover" <[email protected]> wrote:
> >
> >> 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,SMALL(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

On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]> wrote:

>Not sure what you mean?
>
>A1:A6 = {a, b, c, d, e, f}
>B1:B3 = {a, d, f}
>
>how should I reorganize the data to use the formula
>=COUNTIF($A$9:$A$11,B9)=0
>as a criteria in an advance filter and what should I enter in
>List Range box?
>
>Thanks.

OK , you've reversed your lists. And you do have to have things laid out in a
defined way to get the advanced filter to work. Here's one way.

I assume that the result you want is {b,c,e}.

Set up a worksheet as follows

A B
List1 List2
a a
b d
c f
d
e
f

(In columns A & B; and Rows 1-7; note that a header row is used)

E1: <empty>
E2: =COUNTIF($B$2:$B$4,A2)=0

Data/Filter/Advanced Filter
Action: Copy to another location
List Range: $A$1:$B$7
Criteria Range: $E$1:$E$2
Copy to: G1
<OK>

In columns G & H you will see:

List1 List2
b d
c f
e

You are only interested in List1 so you can either delete H1:H3 or copy column
G to wherever. Or set things up to display the results some other way.

The above could also be done using a macro. Probably you should use the macro
recorder if you choose this option.



--ron

Thanks Ron, this works good

When there is an id field and an associated name field this method can
extract only the records in list 1(columns A and B) that do not appear in
list 2 (columns C and D) and then print the result in say columns G and H.
This is a big help.
Thanks,

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]>
> wrote:
>
>>Not sure what you mean?
>>
>>A1:A6 = {a, b, c, d, e, f}
>>B1:B3 = {a, d, f}
>>
>>how should I reorganize the data to use the formula
>>=COUNTIF($A$9:$A$11,B9)=0
>>as a criteria in an advance filter and what should I enter in
>>List Range box?
>>
>>Thanks.
>
> OK , you've reversed your lists. And you do have to have things laid out
> in a
> defined way to get the advanced filter to work. Here's one way.
>
> I assume that the result you want is {b,c,e}.
>
> Set up a worksheet as follows
>
> A B
> List1 List2
> a a
> b d
> c f
> d
> e
> f
>
> (In columns A & B; and Rows 1-7; note that a header row is used)
>
> E1: <empty>
> E2: =COUNTIF($B$2:$B$4,A2)=0
>
> Data/Filter/Advanced Filter
> Action: Copy to another location
> List Range: $A$1:$B$7
> Criteria Range: $E$1:$E$2
> Copy to: G1
> <OK>
>
> In columns G & H you will see:
>
> List1 List2
> b d
> c f
> e
>
> You are only interested in List1 so you can either delete H1:H3 or copy
> column
> G to wherever. Or set things up to display the results some other way.
>
> The above could also be done using a macro. Probably you should use the
> macro
> recorder if you choose this option.
>
>
>
> --ron

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,SMALL(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" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

Thanks Domenic, I could not get the results I wanted, it simply displayed
the same id numbers
repeatedly, rather than including data in adjacent cells.

If I had really huge data sets your method is definitely superior as results
can be printed on one worksheet
and the "long list" and "short list" can be kept in two other worksheets.
I'm sure there is a way to extract data in adjacent cells along with primary
key data, but the data sets I am concerned with are not that large so I
decided to use Ron's filter method (in this thread). To use that method all
the data must be kept on one sheet (there may be a way to use multiple
sheets here too--but I do not know it). The advantage is that the extracted
records can contain data in adjacent cells along with the primary data key.

Les
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> B1, copied down and across:
>
> =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> WS(B$1:B1))),"")
>
> Note that the column reference for...
>
> INDEX('01.0A'!A$1:A$6
>
> ...has been changed to a relative reference.
>
> Hope this helps!
>
> In article <rf4Ke.353$U92.195@okepread06>,
> "Leslie Coover" <[email protected]> wrote:
>
>> 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,SMALL(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

Thanks Ron, this works good

When there is an id field and an associated name field this method can
extract only the records in list 1(columns A and B) that do not appear in
list 2 (columns C and D) and then print the result in say columns G and H.
This is a big help.
Thanks,

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]>
> wrote:
>
>>Not sure what you mean?
>>
>>A1:A6 = {a, b, c, d, e, f}
>>B1:B3 = {a, d, f}
>>
>>how should I reorganize the data to use the formula
>>=COUNTIF($A$9:$A$11,B9)=0
>>as a criteria in an advance filter and what should I enter in
>>List Range box?
>>
>>Thanks.
>
> OK , you've reversed your lists. And you do have to have things laid out
> in a
> defined way to get the advanced filter to work. Here's one way.
>
> I assume that the result you want is {b,c,e}.
>
> Set up a worksheet as follows
>
> A B
> List1 List2
> a a
> b d
> c f
> d
> e
> f
>
> (In columns A & B; and Rows 1-7; note that a header row is used)
>
> E1: <empty>
> E2: =COUNTIF($B$2:$B$4,A2)=0
>
> Data/Filter/Advanced Filter
> Action: Copy to another location
> List Range: $A$1:$B$7
> Criteria Range: $E$1:$E$2
> Copy to: G1
> <OK>
>
> In columns G & H you will see:
>
> List1 List2
> b d
> c f
> e
>
> You are only interested in List1 so you can either delete H1:H3 or copy
> column
> G to wherever. Or set things up to display the results some other way.
>
> The above could also be done using a macro. Probably you should use the
> macro
> recorder if you choose this option.
>
>
>
> --ron

On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]> 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
>

You could also use the Advanced Filter (On the Data menu).

If your data was moved down to A9, then the formula would be:

=COUNTIF($A$9:$A$11,B9)=0


--ron

Works fine, thanks!

Les

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

Make sure that this part of the formula...

INDEX('01.0A'!$A$1:$A$6

....is changed to...

INDEX('01.0A'!A$1:A$6

Therefore, your formulas should be as follows...

A1:

=SUMPRODUCT(--(COUNTIF('01.0B'!$A$1:$A$2,'01.0A'!$A$1:$A$6)=0))

B1, copied down and across:

=IF(ROWS(B$1:B1)<=$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),R
OWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <GGfKe.399$U92.87@okepread06>,
"Leslie Coover" <[email protected]> wrote:

> Thanks Domenic, I could not get the results I wanted, it simply displayed
> the same id numbers
> repeatedly, rather than including data in adjacent cells.
>
> If I had really huge data sets your method is definitely superior as results
> can be printed on one worksheet
> and the "long list" and "short list" can be kept in two other worksheets.
> I'm sure there is a way to extract data in adjacent cells along with primary
> key data, but the data sets I am concerned with are not that large so I
> decided to use Ron's filter method (in this thread). To use that method all
> the data must be kept on one sheet (there may be a way to use multiple
> sheets here too--but I do not know it). The advantage is that the extracted
> records can contain data in adjacent cells along with the primary data key.
>
> Les
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > Try...
> >
> > B1, copied down and across:
> >
> > =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> > WS(B$1:B1))),"")
> >
> > Note that the column reference for...
> >
> > INDEX('01.0A'!A$1:A$6
> >
> > ...has been changed to a relative reference.
> >
> > Hope this helps!
> >
> > In article <rf4Ke.353$U92.195@okepread06>,
> > "Leslie Coover" <[email protected]> wrote:
> >
> >> 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,SMALL(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

On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]> wrote:

>Not sure what you mean?
>
>A1:A6 = {a, b, c, d, e, f}
>B1:B3 = {a, d, f}
>
>how should I reorganize the data to use the formula
>=COUNTIF($A$9:$A$11,B9)=0
>as a criteria in an advance filter and what should I enter in
>List Range box?
>
>Thanks.

OK , you've reversed your lists. And you do have to have things laid out in a
defined way to get the advanced filter to work. Here's one way.

I assume that the result you want is {b,c,e}.

Set up a worksheet as follows

A B
List1 List2
a a
b d
c f
d
e
f

(In columns A & B; and Rows 1-7; note that a header row is used)

E1: <empty>
E2: =COUNTIF($B$2:$B$4,A2)=0

Data/Filter/Advanced Filter
Action: Copy to another location
List Range: $A$1:$B$7
Criteria Range: $E$1:$E$2
Copy to: G1
<OK>

In columns G & H you will see:

List1 List2
b d
c f
e

You are only interested in List1 so you can either delete H1:H3 or copy column
G to wherever. Or set things up to display the results some other way.

The above could also be done using a macro. Probably you should use the macro
recorder if you choose this option.



--ron

On Tue, 9 Aug 2005 23:28:35 -0500, "Leslie Coover" <[email protected]> wrote:

>Thanks Ron, this works good
>
>When there is an id field and an associated name field this method can
>extract only the records in list 1(columns A and B) that do not appear in
>list 2 (columns C and D) and then print the result in say columns G and H.
>This is a big help.
>Thanks,

Glad to help. Thank you for the feedback.
--ron

Try...

B1, copied down and across:

=IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
WS(B$1:B1))),"")

Note that the column reference for...

INDEX('01.0A'!A$1:A$6

....has been changed to a relative reference.

Hope this helps!

In article <rf4Ke.353$U92.195@okepread06>,
"Leslie Coover" <[email protected]> wrote:

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

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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

Hi!

The formula works but the example you posted originally is the opposite of
the example you just posted. So, just reverse some of the references:

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

Array entered.

Biff

"Leslie Coover" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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(COUNTIF($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" <[email protected]> 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

Not sure what you mean?

A1:A6 = {a, b, c, d, e, f}
B1:B3 = {a, d, f}

how should I reorganize the data to use the formula
=COUNTIF($A$9:$A$11,B9)=0
as a criteria in an advance filter and what should I enter in
List Range box?

Thanks.

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]>
> 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
>>
>
> You could also use the Advanced Filter (On the Data menu).
>
> If your data was moved down to A9, then the formula would be:
>
> =COUNTIF($A$9:$A$11,B9)=0
>
>
> --ron

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(COUNTIF($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" <[email protected]> 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

Hi!

The formula works but the example you posted originally is the opposite of
the example you just posted. So, just reverse some of the references:

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

Array entered.

Biff

"Leslie Coover" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>

On Tue, 9 Aug 2005 23:28:35 -0500, "Leslie Coover" <[email protected]> wrote:

>Thanks Ron, this works good
>
>When there is an id field and an associated name field this method can
>extract only the records in list 1(columns A and B) that do not appear in
>list 2 (columns C and D) and then print the result in say columns G and H.
>This is a big help.
>Thanks,

Glad to help. Thank you for the feedback.
--ron

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" <[email protected]> wrote in message
news:[email protected]...
> 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(COUNTIF($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" <[email protected]> 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

Make sure that this part of the formula...

INDEX('01.0A'!$A$1:$A$6

....is changed to...

INDEX('01.0A'!A$1:A$6

Therefore, your formulas should be as follows...

A1:

=SUMPRODUCT(--(COUNTIF('01.0B'!$A$1:$A$2,'01.0A'!$A$1:$A$6)=0))

B1, copied down and across:

=IF(ROWS(B$1:B1)<=$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),R
OWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <GGfKe.399$U92.87@okepread06>,
"Leslie Coover" <[email protected]> wrote:

> Thanks Domenic, I could not get the results I wanted, it simply displayed
> the same id numbers
> repeatedly, rather than including data in adjacent cells.
>
> If I had really huge data sets your method is definitely superior as results
> can be printed on one worksheet
> and the "long list" and "short list" can be kept in two other worksheets.
> I'm sure there is a way to extract data in adjacent cells along with primary
> key data, but the data sets I am concerned with are not that large so I
> decided to use Ron's filter method (in this thread). To use that method all
> the data must be kept on one sheet (there may be a way to use multiple
> sheets here too--but I do not know it). The advantage is that the extracted
> records can contain data in adjacent cells along with the primary data key.
>
> Les
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > Try...
> >
> > B1, copied down and across:
> >
> > =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> > WS(B$1:B1))),"")
> >
> > Note that the column reference for...
> >
> > INDEX('01.0A'!A$1:A$6
> >
> > ...has been changed to a relative reference.
> >
> > Hope this helps!
> >
> > In article <rf4Ke.353$U92.195@okepread06>,
> > "Leslie Coover" <[email protected]> wrote:
> >
> >> 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,SMALL(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

Works fine, thanks!

Les

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

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(COUNTIF($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" <[email protected]> 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

Thanks Domenic, I could not get the results I wanted, it simply displayed
the same id numbers
repeatedly, rather than including data in adjacent cells.

If I had really huge data sets your method is definitely superior as results
can be printed on one worksheet
and the "long list" and "short list" can be kept in two other worksheets.
I'm sure there is a way to extract data in adjacent cells along with primary
key data, but the data sets I am concerned with are not that large so I
decided to use Ron's filter method (in this thread). To use that method all
the data must be kept on one sheet (there may be a way to use multiple
sheets here too--but I do not know it). The advantage is that the extracted
records can contain data in adjacent cells along with the primary data key.

Les
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> B1, copied down and across:
>
> =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> WS(B$1:B1))),"")
>
> Note that the column reference for...
>
> INDEX('01.0A'!A$1:A$6
>
> ...has been changed to a relative reference.
>
> Hope this helps!
>
> In article <rf4Ke.353$U92.195@okepread06>,
> "Leslie Coover" <[email protected]> wrote:
>
>> 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,SMALL(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

Thanks Ron, this works good

When there is an id field and an associated name field this method can
extract only the records in list 1(columns A and B) that do not appear in
list 2 (columns C and D) and then print the result in say columns G and H.
This is a big help.
Thanks,

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]>
> wrote:
>
>>Not sure what you mean?
>>
>>A1:A6 = {a, b, c, d, e, f}
>>B1:B3 = {a, d, f}
>>
>>how should I reorganize the data to use the formula
>>=COUNTIF($A$9:$A$11,B9)=0
>>as a criteria in an advance filter and what should I enter in
>>List Range box?
>>
>>Thanks.
>
> OK , you've reversed your lists. And you do have to have things laid out
> in a
> defined way to get the advanced filter to work. Here's one way.
>
> I assume that the result you want is {b,c,e}.
>
> Set up a worksheet as follows
>
> A B
> List1 List2
> a a
> b d
> c f
> d
> e
> f
>
> (In columns A & B; and Rows 1-7; note that a header row is used)
>
> E1: <empty>
> E2: =COUNTIF($B$2:$B$4,A2)=0
>
> Data/Filter/Advanced Filter
> Action: Copy to another location
> List Range: $A$1:$B$7
> Criteria Range: $E$1:$E$2
> Copy to: G1
> <OK>
>
> In columns G & H you will see:
>
> List1 List2
> b d
> c f
> e
>
> You are only interested in List1 so you can either delete H1:H3 or copy
> column
> G to wherever. Or set things up to display the results some other way.
>
> The above could also be done using a macro. Probably you should use the
> macro
> recorder if you choose this option.
>
>
>
> --ron

On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]> 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
>

You could also use the Advanced Filter (On the Data menu).

If your data was moved down to A9, then the formula would be:

=COUNTIF($A$9:$A$11,B9)=0


--ron

On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" <[email protected]> wrote:

>Not sure what you mean?
>
>A1:A6 = {a, b, c, d, e, f}
>B1:B3 = {a, d, f}
>
>how should I reorganize the data to use the formula
>=COUNTIF($A$9:$A$11,B9)=0
>as a criteria in an advance filter and what should I enter in
>List Range box?
>
>Thanks.

OK , you've reversed your lists. And you do have to have things laid out in a
defined way to get the advanced filter to work. Here's one way.

I assume that the result you want is {b,c,e}.

Set up a worksheet as follows

A B
List1 List2
a a
b d
c f
d
e
f

(In columns A & B; and Rows 1-7; note that a header row is used)

E1: <empty>
E2: =COUNTIF($B$2:$B$4,A2)=0

Data/Filter/Advanced Filter
Action: Copy to another location
List Range: $A$1:$B$7
Criteria Range: $E$1:$E$2
Copy to: G1
<OK>

In columns G & H you will see:

List1 List2
b d
c f
e

You are only interested in List1 so you can either delete H1:H3 or copy column
G to wherever. Or set things up to display the results some other way.

The above could also be done using a macro. Probably you should use the macro
recorder if you choose this option.



--ron

Try...

B1, copied down and across:

=IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
WS(B$1:B1))),"")

Note that the column reference for...

INDEX('01.0A'!A$1:A$6

....has been changed to a relative reference.

Hope this helps!

In article <rf4Ke.353$U92.195@okepread06>,
"Leslie Coover" <[email protected]> wrote:

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

Not sure what you mean?

A1:A6 = {a, b, c, d, e, f}
B1:B3 = {a, d, f}

how should I reorganize the data to use the formula
=COUNTIF($A$9:$A$11,B9)=0
as a criteria in an advance filter and what should I enter in
List Range box?

Thanks.

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]>
> 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
>>
>
> You could also use the Advanced Filter (On the Data menu).
>
> If your data was moved down to A9, then the formula would be:
>
> =COUNTIF($A$9:$A$11,B9)=0
>
>
> --ron

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,SMALL(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" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]> 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
>

You could also use the Advanced Filter (On the Data menu).

If your data was moved down to A9, then the formula would be:

=COUNTIF($A$9:$A$11,B9)=0


--ron

Thanks Domenic, I could not get the results I wanted, it simply displayed
the same id numbers
repeatedly, rather than including data in adjacent cells.

If I had really huge data sets your method is definitely superior as results
can be printed on one worksheet
and the "long list" and "short list" can be kept in two other worksheets.
I'm sure there is a way to extract data in adjacent cells along with primary
key data, but the data sets I am concerned with are not that large so I
decided to use Ron's filter method (in this thread). To use that method all
the data must be kept on one sheet (there may be a way to use multiple
sheets here too--but I do not know it). The advantage is that the extracted
records can contain data in adjacent cells along with the primary data key.

Les
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> B1, copied down and across:
>
> =IF(ROWS(B$1:B1)<$A$1,INDEX('01.0A'!A$1:A$6,SMALL(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),RO
> WS(B$1:B1))),"")
>
> Note that the column reference for...
>
> INDEX('01.0A'!A$1:A$6
>
> ...has been changed to a relative reference.
>
> Hope this helps!
>
> In article <rf4Ke.353$U92.195@okepread06>,
> "Leslie Coover" <[email protected]> wrote:
>
>> 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,SMALL(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

Works fine, thanks!

Les

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

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,SMALL(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" <[email protected]> wrote in message
news:%[email protected]...
> 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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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(COUNTIF($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" <[email protected]> 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
>>
>>
>

Not sure what you mean?

A1:A6 = {a, b, c, d, e, f}
B1:B3 = {a, d, f}

how should I reorganize the data to use the formula
=COUNTIF($A$9:$A$11,B9)=0
as a criteria in an advance filter and what should I enter in
List Range box?

Thanks.

Les

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 Aug 2005 21:29:26 -0500, "Leslie Coover" <[email protected]>
> 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
>>
>
> You could also use the Advanced Filter (On the Data menu).
>
> If your data was moved down to A9, then the formula would be:
>
> =COUNTIF($A$9:$A$11,B9)=0
>
>
> --ron

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(COUNTIF($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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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(COUNTIF($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" <[email protected]> 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
>
>