Sana -
Oct 2, 2015 at 03:35 AM - Latest reply:
TrowaD
2345
Posts
Sunday September 12, 2010Registration dateModeratorStatus
April 23, 2018
Last seen
- Oct 19, 2015 at 11:13 AM

- Comparing Two Columns With other two columns in excel 2003
- How to compare two sheets in excel (Solved) » Forum - Excel
- Excel - Comparing one column data with multiple column » How-To - Excel
- Excel - Comparing multiple columns » How-To - Excel
- How to compare two coloumns in excel (Solved) » Forum - Excel
- A variation on the compare 2 columns in Excel (Solved) » Forum - Excel

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

Was this answer helpful? Yes No

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.

Was this answer helpful? Yes No

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:

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.

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.

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.