Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best Match Approach (XP)

    I have a challenge with matching allocated departments. I have tried putting the absolute values in Col F and sorting run into issues where there are multiple numbers of the same value (hard to match up unless you try to do it manually). I tried a number of approaches but am not satisfied with the results. Perhaps someone can come up with a method.

    In my example:
    Row 15 has department 60060 allocating 258,241.057 to D60056.

    Row 06 as department 60056 receiving the allocation from D60060


    Think of the negative amounts as the department taking a credit and charging or allocating to another department (prefixed with the letter "D").

    Your assistance is appreciated,
    John
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Best Match Approach (XP)

    You could check whether product1 = "D" & department2 AND product2 = "D" & department1 AND amount1 = - amount2.
    It's still possible that there are "real" duplicates.

    If you need further assistance, please attach a small sample workbook.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Best Match Approach (XP)

    I would set up a To/from column concantanated like Hans suggests. But for each row what do you want indicated? Do you want to list what row it goes to or comes from? Or is there a transaction number of some sort in the row that can be displayed?

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best Match Approach (XP)

    Steve,

    It would be best to identiry which row it goes to or comes from.

    Regards,
    John

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Best Match Approach (XP)

    In F5:
    =C5&-E5

    In G5:
    ="D"&A5&-E5

    In H5:
    =IF(ISNUMBER(MATCH(C5&E5,G:G,0)),"To Row"&MATCH(C5&E5,G:G,0),"No 'To Row'")

    In I5:
    =IF(ISNUMBER(MATCH("D"&A5&E5,F:F,0)),"From Row"&MATCH("D"&A5&E5,F:F,0),"No 'From Row'")

    Copy/Autofill G5:I5 down the rest of the column into the rows with data...

    If desired, intermediate columns F and G may be hidden. You can also change the "No To/From" messages as desired: you can eliminate the text between the dbl-quotes to make the cells appear blank.

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best Match Approach (XP)

    Steve,

    Thanks for the suggestion; it appears to be working just fine.

    I'll have to manually trace some of the allocations just to ensure it's doing what it is suppose to.

    Regards,
    John

Posting Permissions

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