Function to determine Alphabetical order of Two String Colomn

[manggo]

Hi Master

Need your help please. First sorry for my bad english.

I have two colomn contain string character, example like this:

Colomn A1: Billy
Colomn B1: Adam
Which is wrong alphabetical order, so in
Colomn C1: equal to 0

It should be:
A1: Adam
B1: Billy
Colomn C1: equal to 1

This in colomn C1 function that I want to ask, how to determine which one (A1 and B1) is the first in alphabetical order?

Thankyou in advanced for your kind help.
Manggo

 

[inactiveuserps07]

@Manggo, even when you have the right formula, it seems like you will need to go back through and switch all the "Wrong" names yourself. This is not needed.

Here is what I would do:

1. Make sure your name lists start in A2:B2 (not A1:B1) so that you can copy formulas exactly.

2. Skip Column C.

3. In cell D2, put this formula:

<trim(b2)&rept(" ",100-len(b2))),trim(a2),trim(b2))
=IF((TRIM(A2)&REPT(" ",100-LEN(A2)) < TRIM(B2)&REPT(" ",100-LEN(B2))),TRIM(A2),TRIM(B2))

4. In cell E2, put this formula:

<trim(b2)&rept(" ",100-len(b2))),trim(b2),trim(a2))
=IF((TRIM(A2)&REPT(" ",100-LEN(A2)) < TRIM(B2)&REPT(" ",100-LEN(B2))),TRIM(A2),TRIM(B2))

5. Click on cell D2, hold down the SHIFT key and click on cell E2, so that both are selected. Then click on the tiny black square in the lower right corner of the thick selection border, hold down the mouse button, and drag the formulas down as far as your name list goes.

This will skip the "Wrong"/"Correct" stage and just keep the names in the right order if they are correct, or swap them if they are wrong.

Then, if you want to replace the original name list in A and B with the new name list in D and E:

1. Select the whole range of names in Columns D and E.

2. Hit Ctrl+C on your keyboard. The area will be selected to be copied.

3. Select the whole range of names in Columns A and B.

4. Hit Ctrl+Alt+V on your keyboard. The PASTE SPECIAL dialog window will open. Click the radio button beside "Values" and then click "OK."

5. Your original list will be replaced with your new list.

6. Then you can just delete all of Column D and E, since the names are now text in A and B without formulas.</trim(b2)&rept("></trim(b2)&rept(">

 

[manggo]

Hi ErikTyler

Yes I wrongly typed Tetra201's formula.

Your formula :
=CHOOSE((TRIM(A2)&REPT(" ",100-LEN(A2)) < TRIM(B2)&REPT(" ",100-LEN(B2)))+1,"Wrong","Correct")

Ahaaa ...Bingo. Its perfectly worked, bro. Such a awesome function that can save my time. Cheers ..

Second of your option:
Yes I want to replace the original name list in A and B with the new name list in D and E.

I followed your instruction step by step.
But the result in D2 and E2 is same ? So I can't paste special value of D and E to original A and B, right ?
Here the file: example3.xlsx - Google Drive

Please advice, perhaps I miss some step.

Can I just exchange only in wrong remarks ?

Thanks ErikTyler.

Manggo

 

[Peter_SSs]

Manggo

If there are extra spaces before or after some of the data, the best thing would be to remove that in the first place if possible.

If removing those extra spaces is not feasible, then I think this simpler formula in C2, copied down should do the check for you.

And if your goal is just to get the names in the right order, you shouldn't even need the check in column C but could try the formulas in columns D & E (and Paste Special - Values back into columns A & B if you want)

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	NAME1	NAME2
2	Adam Alvin	Adam Smith	Correct	Adam Alvin	Adam Smith
3	Adam Smith	Adam Zain	Correct	Adam Smith	Adam Zain
4	Adam Zain	Adam Smith	Wrong	Adam Smith	Adam Zain
5	Adam Smith	Adam Alvin	Wrong	Adam Alvin	Adam Smith
6	Adam Alvin	Adam Alvim	Wrong	Adam Alvim	Adam Alvin
7	Adam Alvin	Adam Alvis	Correct	Adam Alvin	Adam Alvis
Check Order

Cell Formulas
Range	Formula
C2	=IF(TRIM(A2)>TRIM(B2),"Wrong","Correct")
D2	=IF(TRIM(A2)>TRIM(B2),TRIM(B2),TRIM(A2))
E2	=IF(TRIM(A2)B2),TRIM(B2),TRIM(A2))

 

[inactiveuserps07]

@Manggo, I accidentally put the same formula up twice! Sorry about that. Just change the formula in cell E2 to this:

=IF((TRIM(A2)&REPT(" ",100-LEN(A2)) < TRIM(B2)&REPT(" ",100-LEN(B2))),TRIM(B2),TRIM(A2))

Then click cell E2 and drag that small black square down to copy the formula into the cells below.


However, Peter is correct above.

At first, I modified a formula based on a previous poster and wound up adding a step you don't need (adding s the REPT space at the end).

So Peter's formulas cut out that step. I'd recommend using them.

 

[manggo]

Hi Master
Erik, Tetray, Peter.. you all so kind. My problem solved now. The formula is working like a charm.
Of course, my time on checking is not a big deal anymore.. salute to you all.
Billion thanks my brother for your help.

My case closed then.

Manggo

 

[Peter_SSs]

Glad you got a successful outcome.