Need a formula for the no matching items in a worksheet with the following
data:
Columns A, B and C are the data for 2008
Column A- Customer number
Column B €“ Account number
Column C €“ Balance in ‚¬
Columns D, E and F data for 2007
Note that it is possible to have customers in 2008 without balance in 2007
and customers in 2007 that don´t have balance in 2008
One customer can have more than one account and different accounts for 2007
and 2008.
What I need is to retrieve in columns G, H and I customers of 2008 that do
not match 2007 for customer and/or account.
Ex.
A B C D E F G H I
110032 228 100 110052 268 85 110032 228 100
110032 268 76 110059 228 87 110032 268 76
110052 268 70 110142 221 88 110052 221 74
110052 221 74 110302 228 77 110102 221 79
110102 221 79 110142 228 81
110142 228 81
110302 228 65

Thanks in advance for your support

If you want to get this list using worksheet functions..try the below. OR if
you are looking for a macro please post back..

Insert a column after 2008 data; so that you have a blank column D. Paste
the below formula to D1. Copy it down...This will identify all the entries
which are there in 2007. Sort to ones with 0 value...(not present in 2007
data)

=SUMPRODUCT((A1=E:E)*(B1=F:F))

If this post helps click Yes
---------------
Jacob Skaria


"henriques" wrote:

Need a formula for the no matching items in a worksheet with the following
data:
Columns A, B and C are the data for 2008
Column A- Customer number
Column B €“ Account number
Column C €“ Balance in ‚¬
Columns D, E and F data for 2007
Note that it is possible to have customers in 2008 without balance in 2007
and customers in 2007 that don´t have balance in 2008
One customer can have more than one account and different accounts for 2007
and 2008.
What I need is to retrieve in columns G, H and I customers of 2008 that do
not match 2007 for customer and/or account.
Ex.
A B C D E F G H I
110032 228 100 110052 268 85 110032 228 100
110032 268 76 110059 228 87 110032 268 76
110052 268 70 110142 221 88 110052 221 74
110052 221 74 110302 228 77 110102 221 79
110102 221 79 110142 228 81
110142 228 81
110302 228 65

Thanks in advance for your support

I got some 0, 1 and 2 and it seems that the non matches are not OK. Please
send me the macro.

"Jacob Skaria" wrote:

If you want to get this list using worksheet functions..try the below. OR if
you are looking for a macro please post back..

Insert a column after 2008 data; so that you have a blank column D. Paste
the below formula to D1. Copy it down...This will identify all the entries
which are there in 2007. Sort to ones with 0 value...(not present in 2007
data)

=SUMPRODUCT((A1=E:E)*(B1=F:F))

If this post helps click Yes
---------------
Jacob Skaria


"henriques" wrote:

Need a formula for the no matching items in a worksheet with the following
data:
Columns A, B and C are the data for 2008
Column A- Customer number
Column B €“ Account number
Column C €“ Balance in ‚¬
Columns D, E and F data for 2007
Note that it is possible to have customers in 2008 without balance in 2007
and customers in 2007 that don´t have balance in 2008
One customer can have more than one account and different accounts for 2007
and 2008.
What I need is to retrieve in columns G, H and I customers of 2008 that do
not match 2007 for customer and/or account.
Ex.
A B C D E F G H I
110032 228 100 110052 268 85 110032 228 100
110032 268 76 110059 228 87 110032 268 76
110052 268 70 110142 221 88 110052 221 74
110052 221 74 110302 228 77 110102 221 79
110102 221 79 110142 228 81
110142 228 81
110302 228 65

Thanks in advance for your support

Dear Jacob

Your formula works. I have just make a mistake when I coopied the formula to
my worksheet. The only problem is that the process is very slow because the
file has more then 3.000 lines. I don´t know if the macro speeds the procees.

Thanks any way.

"Jacob Skaria" wrote:

If you want to get this list using worksheet functions..try the below. OR if
you are looking for a macro please post back..

Insert a column after 2008 data; so that you have a blank column D. Paste
the below formula to D1. Copy it down...This will identify all the entries
which are there in 2007. Sort to ones with 0 value...(not present in 2007
data)

=SUMPRODUCT((A1=E:E)*(B1=F:F))

If this post helps click Yes
---------------
Jacob Skaria


"henriques" wrote:

Need a formula for the no matching items in a worksheet with the following
data:
Columns A, B and C are the data for 2008
Column A- Customer number
Column B €“ Account number
Column C €“ Balance in ‚¬
Columns D, E and F data for 2007
Note that it is possible to have customers in 2008 without balance in 2007
and customers in 2007 that don´t have balance in 2008
One customer can have more than one account and different accounts for 2007
and 2008.
What I need is to retrieve in columns G, H and I customers of 2008 that do
not match 2007 for customer and/or account.
Ex.
A B C D E F G H I
110032 228 100 110052 268 85 110032 228 100
110032 268 76 110059 228 87 110032 268 76
110052 268 70 110142 221 88 110052 221 74
110052 221 74 110302 228 77 110102 221 79
110102 221 79 110142 228 81
110142 228 81
110302 228 65

Thanks in advance for your support