Comparing Two Columns With other two columns in excel 2003
Solved/Closed
Sana
-
Oct 2, 2015 at 03:35 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 19, 2015 at 11:13 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 19, 2015 at 11:13 AM
Related:
- Comparing Two Columns With other two columns in excel 2003
- Display two columns in data validation list but return only one - Guide
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- Dd-mm-yyyy format in excel - Guide
- Excel free download - Download - Spreadsheets
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 15, 2015 at 10:52 AM
Oct 15, 2015 at 10:52 AM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 19, 2015 at 11:13 AM
Oct 19, 2015 at 11:13 AM
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)
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
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
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
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 5, 2015 at 12:13 PM
Oct 5, 2015 at 12:13 PM
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
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
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
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
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.
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.
Oct 17, 2015 at 03:56 AM
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.