Common value key/relationship between 2 cells

[And21]

Hi all,

I have built a report with some financial transactions checking.

The scenario is, (col A) company "1" sends money to (col B) company "2" so the common value in column C will be "12" (cell A & cell B).
But if the company "2" sends money to "1" then in column C we will have "21".

What I need is a formula in a new column that identifies the relationship between the companies and returning back a common value.
For example, IF column A & column B = 12 then return 12
and IF column A & column B = 21 then return again 12 ( and not 21)

I hope that my description is clear.

Thank you,

 

[SanjayGMusafir]

What I understand from your description, a simple formula like below should do the job -

=IF(B2>A2,A2&B2,B2&A2)

 

[And21]

What I understand from your description, a simple formula like below should do the job -

=IF(B2>A2,A2&B2,B2&A2)

Hi, thanks for your reply.

However, in the real scenario there are 4 digits codes e.g company with code "1234" to company "4040". So, the common key in column C = "12344040"
and in case of vice versa transaction (4040 to 1234) in column C = "40401234". Let's say that we have 10 different companies with 10 uniques codes.

The problem is how to get a common value for the relationship between them. 1234-4040 or 4040-1234 connected only with 12344040 in col C
My purpose is to create a pivot table in the end with all the company groups.

 

[SanjayGMusafir]

real scenario there are 4 digits codes

Share sample data using XL2BB that matches the real scenario

 

[And21]

Hi, sorry it seems that XL2BB doesn't work with my excel. Until I find a solution, here a screenshot of the project.
Only in column C there is a formula: A1&A2. The problem is in the pivot because we have multiple times the same relationship, just in a different order.

thanks

 

[SanjayGMusafir]

In this case you can use Column D as clue and in C2 use

=IF(D2>0,A2&B2,B2&A2)

OR

=IF(D2<0,A2&B2,B2&A2)

whichever suits you...

Either ways you will have a common code where both parties are same thus giving you a Pivot Table with Net summary.

Hope it works for you...

 

[And21]

Not really because there is not a pattern in the amounts in order to link it with the codes.
What I would like to have is, for example the common key for A1+B1 = 60604040, but A5+B5 could be 40406060 in this case I want in column C to return 60604040 again and not 40406060 - because if you see in the Pivot I need to have the unique relationship of the codes in order to have its sum of the amounts, whatever the codes are... e.g 3020 to 2020 or 2020 3020 every time to return 30202020.

*I have 10 codes making pairs each other

 

[SanjayGMusafir]

Not really because there is not a pattern in the amounts in order to link it with the codes.
What I would like to have is, for example the common key for A1+B1 = 60604040, but A5+B5 could be 40406060 in this case I want in column C to return 60604040 again and not 40406060 - because if you see in the Pivot I need to have the unique relationship of the codes in order to have its sum of the amounts, whatever the codes are... e.g 3020 to 2020 or 2020 3020 every time to return 30202020.

*I have 10 codes making pairs each other

Have you tried the formulae? They are for that purpose only...

 

[And21]

I used in column E: IF(D2>0,A2&B2,B2&A2), but when we have more positive amounts like the highlighted , it doesn't return the correct value. Maybe I need to add more conditions in IF

 

[SanjayGMusafir]

In the example you last quoted, second last row has different sender and receiver codes else formula is working fine. Check that.