Common value key/relationship between 2 cells

And21

New Member
Joined
Sep 23, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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)


Screenshot 2022-09-23 at 6.04.04 PM.png



I hope that my description is clear.

Thank you,
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
What I understand from your description, a simple formula like below should do the job -

Excel Formula:
=IF(B2>A2,A2&B2,B2&A2)
 
Upvote 0
What I understand from your description, a simple formula like below should do the job -

Excel Formula:
=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.
 
Upvote 0
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.

Screenshot 2022-09-23 at 8.22.29 PM.png


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

Excel Formula:
=IF(D2>0,A2&B2,B2&A2)

OR

Excel Formula:
=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...
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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

Screenshot 2022-09-24 at 12.30.22 PM.png
 
Upvote 0
In the example you last quoted, second last row has different sender and receiver codes else formula is working fine. Check that.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top