I have a mailing list of 2,265 names. Someone has randomly seleceted 1,000
names from the list and mailed them. I need to mail the other 1,265.

I have two Excel workbooks with 2,265 names in one and 1,000 in the other.

I need to be able to subtract the data in the smaller workbook from the
larger one.

Any ideas please??

With data in Sheet1 (2265) and Sheet2 (1000) ; apply the below formula in
Sheet1 cell B1 and copy down to row 2265.....

=IF(COUNTIF(Sheet2!A:A,A1),"","Mail to be sent")

--
Jacob (MVP - Excel)


"Phil C." wrote:

I have a mailing list of 2,265 names. Someone has randomly seleceted 1,000
names from the list and mailed them. I need to mail the other 1,265.

I have two Excel workbooks with 2,265 names in one and 1,000 in the other.

I need to be able to subtract the data in the smaller workbook from the
larger one.

Any ideas please??

Hi Jacob

Thanks for this idea. Can I change this formula so that it removes the 1000
names and just leaves me with a datasheet with 1,265 names?

Thanks

Phil



"Jacob Skaria" wrote:

With data in Sheet1 (2265) and Sheet2 (1000) ; apply the below formula in
Sheet1 cell B1 and copy down to row 2265.....

=IF(COUNTIF(Sheet2!A:A,A1),"","Mail to be sent")

--
Jacob (MVP - Excel)


"Phil C." wrote:

I have a mailing list of 2,265 names. Someone has randomly seleceted 1,000
names from the list and mailed them. I need to mail the other 1,265.

I have two Excel workbooks with 2,265 names in one and 1,000 in the other.

I need to be able to subtract the data in the smaller workbook from the
larger one.

Any ideas please??

In a different sheet; say sheet3 cell A1 enter the formula and copy
down...which will retrive all the ones which are not present in Sheet2 (1000)
with blank cells..inbetween.
=IF(ISNA(MATCH(Sheet1!A1,Sheet2!A:A,0)),Sheet1!A1, "")

OR try this array formula in Sheet3 cell A1 and copy down as required . This
will list out all missing ones in Sheet2 in sequence (without blank cells).
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=INDEX(Sheet1!$A$1:$A$2265,SMALL(IF(ISNA(MATCH(
Sheet1!$A$1:$A$2265,Sheet2!$A$1:$A$1000,0)),
ROW(Sheet1!$A$1:$A$2265)),ROW(A1)))

--
Jacob (MVP - Excel)


"Phil C." wrote:

Hi Jacob

Thanks for this idea. Can I change this formula so that it removes the 1000
names and just leaves me with a datasheet with 1,265 names?

Thanks

Phil



"Jacob Skaria" wrote:

With data in Sheet1 (2265) and Sheet2 (1000) ; apply the below formula in
Sheet1 cell B1 and copy down to row 2265.....

=IF(COUNTIF(Sheet2!A:A,A1),"","Mail to be sent")

--
Jacob (MVP - Excel)


"Phil C." wrote:

I have a mailing list of 2,265 names. Someone has randomly seleceted 1,000
names from the list and mailed them. I need to mail the other 1,265.

I have two Excel workbooks with 2,265 names in one and 1,000 in the other.

I need to be able to subtract the data in the smaller workbook from the
larger one.

Any ideas please??

Thanks Jacob.

That is seriously clever!

Cheers. Phil

"Jacob Skaria" wrote:

In a different sheet; say sheet3 cell A1 enter the formula and copy
down...which will retrive all the ones which are not present in Sheet2 (1000)
with blank cells..inbetween.
=IF(ISNA(MATCH(Sheet1!A1,Sheet2!A:A,0)),Sheet1!A1, "")

OR try this array formula in Sheet3 cell A1 and copy down as required . This
will list out all missing ones in Sheet2 in sequence (without blank cells).
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=INDEX(Sheet1!$A$1:$A$2265,SMALL(IF(ISNA(MATCH(
Sheet1!$A$1:$A$2265,Sheet2!$A$1:$A$1000,0)),
ROW(Sheet1!$A$1:$A$2265)),ROW(A1)))

--
Jacob (MVP - Excel)


"Phil C." wrote:

Hi Jacob

Thanks for this idea. Can I change this formula so that it removes the 1000
names and just leaves me with a datasheet with 1,265 names?

Thanks

Phil



"Jacob Skaria" wrote:

With data in Sheet1 (2265) and Sheet2 (1000) ; apply the below formula in
Sheet1 cell B1 and copy down to row 2265.....

=IF(COUNTIF(Sheet2!A:A,A1),"","Mail to be sent")

--
Jacob (MVP - Excel)


"Phil C." wrote:

I have a mailing list of 2,265 names. Someone has randomly seleceted 1,000
names from the list and mailed them. I need to mail the other 1,265.

I have two Excel workbooks with 2,265 names in one and 1,000 in the other.

I need to be able to subtract the data in the smaller workbook from the
larger one.

Any ideas please??

Jacob

A little late but congratulations on MVP status.


Gord

On Wed, 14 Apr 2010 03:56:01 -0700, Jacob Skaria
wrote:

With data in Sheet1 (2265) and Sheet2 (1000) ; apply the below formula in
Sheet1 cell B1 and copy down to row 2265.....

=IF(COUNTIF(Sheet2!A:A,A1),"","Mail to be sent")

Thanks Gord.


"Gord Dibben" wrote:

Jacob

A little late but congratulations on MVP status.


Gord

On Wed, 14 Apr 2010 03:56:01 -0700, Jacob Skaria
wrote:

With data in Sheet1 (2265) and Sheet2 (1000) ; apply the below formula in
Sheet1 cell B1 and copy down to row 2265.....

=IF(COUNTIF(Sheet2!A:A,A1),"","Mail to be sent")

.