Looking to compare 2 Column and find closest match then sort

[skyfleo]

hello , I have this 2 list ,and I want the same word to be in the same row ( Ex : row 1 contains : "Service 351" in A1 and "Service 82" in B1 ) , please help and ty ♥

 

[Anthony47]

If you separately sort each of the two columns, would you get the result that you need?

 

[skyfleo]

I need to keep those numbers , each row should contain 2 same names + the numbers like this :

 

[Anthony47]

And wouldn't sorting each of the columns get that result?

 

[skyfleo]

And wouldn't sorting each of the columns get that result?

nop the second list has more cells than the first one

 

[Anthony47]

Try in D1:

=INDEX($A$1:$A$100,MIN(IF((LEFT(SUBSTITUTE($A$1:$A$100," ",REPT(" ",50)),50)=LEFT(SUBSTITUTE(B1," ",REPT(" ",50)),50)),ROW($A$1:$A$100),"")))

To be confirmed by Contr-Shift-Enter, nor Enter alone
This assume a max of 100 items in column A; adapt those $100 to higher value, in case that 100 is not enough

In E1, of course:
=B1

 

[skyfleo]

it

Try in D1:

=INDEX($A$1:$A$100,MIN(IF((LEFT(SUBSTITUTE($A$1:$A$100," ",REPT(" ",50)),50)=LEFT(SUBSTITUTE(B1," ",REPT(" ",50)),50)),ROW($A$1:$A$100),"")))

To be confirmed by Contr-Shift-Enter, nor Enter alone
This assume a max of 100 items in column A; adapt those $100 to higher value, in case that 100 is not enough

In E1, of course:
=B1

Nothing happen the 2 column are still the same not sorted like I want :/

 

[Anthony47]

Copy the formula down

 

[skyfleo]

it

View attachment 64873
Nothing happen the 2 column are still the same not sorted like I want :/

 

[Anthony47]

Retry:

=IFERROR(INDEX($A$1:$A$100,IF(MIN(IF((LEFT(SUBSTITUTE($A$1:$A$100," ",REPT(" ",50)),50)=LEFT(SUBSTITUTE(B1," ",REPT(" ",50)),50)),ROW($A$1:$A$100),""))=0,-1,MIN(IF((LEFT(SUBSTITUTE($A$1:$A$100," ",REPT(" ",50)),50)=LEFT(SUBSTITUTE(B1," ",REPT(" ",50)),50)),ROW($A$1:$A$100),"")))),"")

Using Office 365 its lenght would be cut by 3