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,

 

[Fluff]

How about

=MIN(A2,B2)&MAX(A2,B2)

 

[And21]

Nop, because for example the last row in column E should give us a common value (the same as the above: 60604040).

 

[And21]

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

Nop, because for example the last row in column E should give us a common value (the same as the above: 60604040).

 

[And21]

How about

=MIN(A2,B2)&MAX(A2,B2)

Hi, interesting, in the testing file it seems fine.
I will test it also in the real scenario to see if it works.
thanks

 

[Fluff]

Ok

 

[And21]

Ok

Ok

It works !! Thanks a lot !

 

[And21]

Thank you !!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[SanjayGMusafir]

Not creating any controversy as I have learnt so much from @Fluff in this forum itself. But just to mention and for your understanding -

The formula I initially gave

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

and the formula @Fluff gave

=MIN(A2,B2)&MAX(A2,B2)

shall produce exactly same results. Same thing presented using different formula.

Check this -

Book1.xlsx
	A	B	C	D
1	Party 1	Party 2	SGM	Fluff
2	4040	5050	40405050	40405050
3	5050	4040	40405050	40405050
4	9090	6060	60609090	60609090
5	6060	9090	60609090	60609090
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(B2>A2,A2&B2,B2&A2)
D2:D5	D2	=MIN(A2,B2)&MAX(A2,B2)

 

[And21]

Not creating any controversy as I have learnt so much from @Fluff in this forum itself. But just to mention and for your understanding -

The formula I initially gave

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

and the formula @Fluff gave

=MIN(A2,B2)&MAX(A2,B2)

shall produce exactly same results. Same thing presented using different formula.

Check this -

Book1.xlsx
	A	B	C	D
1	Party 1	Party 2	SGM	Fluff
2	4040	5050	40405050	40405050
3	5050	4040	40405050	40405050
4	9090	6060	60609090	60609090
5	6060	9090	60609090	60609090
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(B2>A2,A2&B2,B2&A2)
D2:D5	D2	=MIN(A2,B2)&MAX(A2,B2)

Hi Sanjay,

Indeed, it works as well. I really appreciate spending your time to help on this. Thank you!