+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    07-12-2006
    Posts
    14

    Question 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. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    07-12-2006
    Posts
    14

    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. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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.

    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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