# Comparing rows of data - A bit trickier than normal!!

1. ## Comparing rows of data - A bit trickier than normal!!

I need to compare rows on 2 separate worksheets.
I have an internal order worksheet (sheet 1)
I also have a head office shipping report.

The tricky bit is the data is often duplicated on the sheets.

Example:
Column A is the Order number
Column B is the Part number.
Column C is the Part number quantity

Under column A I may have the following order numbers:
Order # 123
Order # 456
Order # 789

Under column B I may have the following part numbers:
A112233
B445566
C778899

Now the tricky bit is:

Order Number # 123 may contain the following part numbers:
A112233
C778899

Order Number # 456 may contain the following part numbers:
B445566
C778899

Order Number # 789 may contain the following part numbers:
A112233
B445566
C778899

What I need to do is compare only Order Number #123, part number A112233 on sheet 1,
with Order Number # 123, Part Number A112233 on sheet 2

When comparing the lists I do NOT want to compare Order Number # 123, part number A112233 on sheet 1,
with Order Number # 789, part number A112233 on sheet 2.

Make sense??? Basically I want to compare apples with apples but not with oranges

I hope so and if any of you geniuses out there can figure this one out for me I will owe you my eternal gratitude!!

2. Originally Posted by leapyleigh
I need to compare rows on 2 separate worksheets.
I have an internal order worksheet (sheet 1)
I also have a head office shipping report.

The tricky bit is the data is often duplicated on the sheets.
Did you mean that parts are supplied bit-meal, where many isues may fill an order?

Example:
Column A is the Order number
Column B is the Part number.
Column C is the Part number quantity

Under column A I may have the following order numbers:
Order # 123
Order # 456
Order # 789

Under column B I may have the following part numbers:
A112233
B445566
C778899
What I need to do is compare only Order Number #123, part number A112233 on sheet 1,
with Order Number # 123, Part Number A112233 on sheet 2

When comparing the lists I do NOT want to compare Order Number # 123, part number A112233 on sheet 1,
with Order Number # 789, part number A112233 on sheet 2.
try (in a separate column, cell ~2)

=C2-SUMPRODUCT(--(Sheet4!A2:A50000=A2)*(--(Sheet4!B2:B50000=B2))*Sheet4!C2:C50000)

CSE (Ctrl/Shift/Enter) then formula fill down the column to the extent of your data

where C2 is the Quantity, A2 and B2 to the extent of your data (A50000 and B50000) are Order and Part, amend the Sheetname to suit, note, the data ranges 2:50000 must all be the same, if you amend one, amend all.

hth
---

3. ## You little beauty!!

Thank-you thank-you!!

Shall I bottle your blood now or later???

I owe you big time!! If you are ever in Melbourne Australia I owe you a roast dinner!!

Thanks once again for saving me hours of labourious drudgery!!

4. Originally Posted by leapyleigh
Thank-you thank-you!!

Shall I bottle your blood now or later???

I owe you big time!! If you are ever in Melbourne Australia I owe you a roast dinner!!

Thanks once again for saving me hours of labourious drudgery!!
Thanks for your response, I gather it worked for you.

The last time I was in Melbourne was when the gas supply pipe froze over, and your lovely Jeff Kennett announced on the radio that he didn't think it got cold in Melbourne.

---

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1