Report

Comparing Two Columns With other two columns in excel 2003 [Solved/Closed]

Ask a question Sana - Last answered on Oct 19, 2015 11:13AM
Dear Sir,
I want to compare two columns with other two columns in excel 2003.
Columns are as below
A B C D
a 10 a 10
b 20 b 30
c 30 d 40
d 40 e 50
e 50 i 3
I want to know that column A and B values match with column C and D. If yes then show match if no show unique.
Please help me in this regard. I will be very thankful to you.
Thanks in advance.
See more 
Helpful
+3
moins plus
Hi Sana,

I don't see a difference between your first request and your latest.

Could you show how you foresee the result to be, after your sample data has been processed?

Best regards,
Trowa
Sana- Oct 17, 2015 03:56AM
Dear Trowad,

Here is the sample data. It is about the purchase order's balance quantity. We are buyer and we give purchase order to seller. We import different quantity of different items and record our balance order quantity. Our seller also record the balance quantity and send us to match these with those we have recorded. So as the list is too big i want here the following things.
1- Match both buyer's Purchase order items and quantity with seller's items and quantity.
2- If there is any unique values, want to know which are not in buyers so that i add those in buyers list.
3-Which items and quantity are in buyers list but not in sellers so that i inform them to add in your (seller ) list.

As the list is too big i want some shortcuts in the form of formula to save my time.

Buyer's Purchase order balance Seller's order balance
Items Quantity (balance) Items Quantity (balance)
A010 100 A010 100
B020 225 B020 225
C02V 320 C02V 32
F0320 600 CC020 156
RTO10 200 RTO10 200
EFGTL 65 EFGTL 23
BCVB 45

Hope this help.
Thanks.
Helpful
+3
moins plus
Hi Sana,

So if this is your sample data:
Buyer's Purchase order balance Seller's order balance
Items Quantity (balance) Items Quantity (balance)
A010 100 A010 100
B020 225 B020 225
C02V 320 C02V 32
F0320 600 CC020 156
RTO10 200 RTO10 200
EFGTL 65 EFGTL 23
BCVB 45

Then how do you foresee the result to look like?

Something like this?:
Buyer's Purchase order balance Seller's order balance
Items Quantity (balance) Items Quantity (balance)
A010 100 A010 100
B020 225 B020 225
C02V 320  
  C02V 32
F0320 600  
  CC020 156
RTO10 200 RTO10 200
EFGTL 65  
  EFGTL 23
BCVB 45


So basically compare column A & B with C & D and if there is a difference, create a new row to split the data from 1 row to 2 rows.

Best regards,
Trowa

Helpful
+2
moins plus
Hi Sana,

Try the following array formula:
=SUM(($A$1:$A$5=A1)*($B$1:$B$5=B1)+($C$1:$C$5=A1)*($D$1:$D$5=B1))
Note: array formula's need to be confirmed by hitting Ctrl+Shift+Enter. When done correctly the formula will start and end with curly brackets {}.

Formula will show a 1 for Unique and a 2 for a Match.

Best regards,
Trowa
Sana- Oct 9, 2015 05:40AM
Hi Trowad,
First of all thanks for your help.
Your formula worked but is it possible to have unique values of a and b column which are not in c and d column. In other words i want to compare the unique values of first two columns with other two columns to find the values which are not in first two columns or as well.
Again thanks for your help.

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!