# Thread: Best Match Approach (XP)

1. ## 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").

John

2. ## 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. ## 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. ## Re: Best Match Approach (XP)

Steve,

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

Regards,
John

5. ## 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. ## 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
•