A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula for non matching items



 
 
Thread Tools Display Modes
  #1  
Old April 12th, 2009, 03:30 PM posted to microsoft.public.excel.worksheet.functions
henriques
external usenet poster
 
Posts: 13
Default Formula for non matching items

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
  #2  
Old April 12th, 2009, 06:38 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Formula for non matching items

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

  #3  
Old April 13th, 2009, 12:35 PM posted to microsoft.public.excel.worksheet.functions
henriques
external usenet poster
 
Posts: 13
Default Formula for non matching items

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

  #4  
Old April 13th, 2009, 05:53 PM posted to microsoft.public.excel.worksheet.functions
henriques
external usenet poster
 
Posts: 13
Default Formula for non matching items

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.