Unique Match formula requirement

[Deon_van]

Hi, 

I have 3 datasets that I need to combine based on PC and PPC combination / matches. 

The 1 input effectively shows me revenue received against a specific GL (revenue) account in a region, and against the other 2 regions that pick-up the expense on separate GL account.

The idea is to match the profit center (PC) & cost center (PPC) combination and then determine the elimination that should be processed against these accounts. The difference column will determine which GL account should be used as the higher amount should always be used for the elimination. There are various reasons why they might not match 100%.  

The problem I'm having is that in the 1 region 2 different opex accounts are used (valid reason to distinguish a specific product portion of the charge) but that should match against the region (1) receiving the revenue. 

Below a screenshot of the problem, I'm having. The sum of the 2 amounts highlighted should match the -120 on the left. The duplicate -120 on record 4 is fictitious and not valid (there is only 1 x -120). Thus, is there a method to avoid that duplicate showing so the math will work, and the necessary journals can be posted after this. 

Extra detail below as FYI:

The journal for record 3 would then effectively be:

1. Acc 496971 to be credited with -25 against a specific cost center

2. Acc 496958 to be credited with -100 against a specific cost center

3. Acc 833766 to be debited with 125 against a specific cost center, seeing the opex was higher than the revenue.

Journal for record 1 would effectively be:

1. Acc 496958 to be credited with -530 against a specific center

2. Acc 833766 to be debited with 530 against a specific center. 

Your feedback will be appreciated. Thanks

[griffinwelsh]

@Deon_van If I understand the requirement correctly you need to replace the duplicate value(s) with 0s.You can do this with a multirow formula tool as shown in the screenshot and attached revised workflow.

 

[Deon_van]

hi griffinwelsh,

unfortunately, that won't work, as the lines aren't always combined or below one another, and the rest of the process will then also result in incorrect results being calculated. 

I almost need to be able to create a unique combo for the INT_PC & PPC and SA_AR_PPC & PC to allow for the numbers to add up irrespective of the cost center, but once it has done the calc I somehow need to bring back the cost center then to do the journal once the rest of the process is done, which isn't included in this.

Not sure if that helps