Formula that Adds Values from Matching Concatenates (Pics Work Now)

Another intern and I are reconciling accounts from the old system to the new system for the company we are working for over the summer. We have already created a template using the match and concatenate functions that identifies the accounts that perfectly match from old to new. The next step is where a formula would come in handy ...(see picture)

http://s1027.photobucket.com/user/jo...osjph.png.html

The columns on the left represent the new system. Only department and account numbers were used because the new system is more condensed than the old and other classifications like location do not match with new vs. old. Department and account numbers are still the same and is what we used for the concatenates (we also used amount in the first part as part of the concatenate to ensure a perfect match). As you can see by the first picture, the accounts circled in red match. The values on the right added together match the value on the left while also matching the department and account numbers...easily reconciled. It gets more difficult though.

http://s1027.photobucket.com/user/jo...qq0bt.png.html

In some cases, there are multiple accounts in the new system that reconcile with one account in the old system. This is rare, but makes creating a formula to ease this process too difficult for us .

What we need:

1. A formula that matches the concatenates of the new column the adds the values of the numbers in the preceding cells. Using the picture:
Matches the reds, adds the yellows, and puts the value in the pink cell. If there is no concatenate match, the value in the pink cell would just be the value in the amount cell left of the concatenate cell...obviously.

http://s1027.photobucket.com/user/jo...fhffz.png.html

2. We need the same thing for the old columns.

3.Create concatenates including the department, account, and the value of the pink cell on both sides:

http://s1027.photobucket.com/user/jo...qu3qi.png.html

We would then run a match for the new concatenates.

We are also open to other ideas that might make this process easier. We have 60 more of these to do which roughly equates to 90 hours of work. Any help at all would be appreciated.

Thank you,
John and ConnorExcel Example.xlsx

I'm a bit lost here!!

I don't see how you can get the two values (one +ve, one -ve) for 375 from the data in OLD.

Does this start you off, though?

In C3 dragged down...

=SUMIFS(I:I,G:G,A3,H:H,B3)

... But (with the exception of 375), it gave the same results that had been in the cells. In what way is that incorrect???

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

if you need any further help -don't be afraid to shout!!