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

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.

See more
+3
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-

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.
+3
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

+2
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-
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.

Member requests are more likely to be responded to.

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