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, in B1:

<value(code(left(b1))&code(mid(b1,2,1))&code(mid(b1,3,1)))))
<value(code(left(b1))&code(mid(b1,2,1))&code(mid(b1,3,1)))))[ code]
=SUMPRODUCT(--(VALUE(CODE(MID(A1,{1,2,3},1))) < VALUE(CODE(MID(B1,{1,2,3},1)))))

This will check for alphabetical order to 3 characters into each word, so it can tell the difference between

BILLY ... ADAM (different from 1st character)

GRAB ... GIFT (different from 2nd character)

LOVE ... LOSS (different from 3rd character)

but not between

AN ... AT (too short)

ABOUT ... ABOVE (difference isn't until 4th character)

Of course, you could just lengthen the formula to catch any number of characters.

I think the formula should work the way you want, though, for most or all cases.</value(code(left(b1))&code(mid(b1,2,1))&code(mid(b1,3,1)))))[></value(code(left(b1))&code(mid(b1,2,1))&code(mid(b1,3,1)))))

 

[manggo]

Thank you ErikTyler for your quick response. Very appreciate.

I tried your function but I don't understand how is goes. Sorry brother, I am just new in excel formula.
My goal is just to know the string character that typed in Colomn A and Colomn B is alphabetically order or not,
that will explain in Colomn C.

Would you please take a look of myfile (example1.xlxs). I cant upload here (?) so i put on my google drive.
Please see your function I copied on J12

example1.xlsx - Google Drive

Ton of thanks ErikTyler for your kind help

Manggo

 

[inactiveuserps07]

@Manggo,

It looks like you only care about comparing the first letter of each name.

Try this:

=N(CODE(A12) < CODE(B12))<code(b12))[ code]<="" html=""></code(b12))[>

 

[manggo]

Thanks Erik..

Yes, on my vlookup function only able to check the first letter. So as like I only care for first letter. But
I want not only first letter of the name, but whole letters should be check also.

I have thousands pair name(name1 and name2) that I have to check whether they are alphabetical order or not that would mark in colomn C.

These my example on plain text.

On excel :
Colomn A: Name1
Colomn B: Name2
ColomnC: Remarks, CORRECT, mean let it be. WRONG, mean do exchange of name1 and name2, put name2 to name1, vice versa.

Order in excel
Name1. ~ Name2 ~ Remarks
Adam Alvin ~ Adam Smith ~ Correct
Adam Smith ~ Adam Zain ~ Correct
Adam Zain ~ Adam Smith ~ Wrong
Adam Smith ~ Adam Alvin ~ Wrong
Adam Alvin ~ Adam Alvim ~ Wrong
Adam Alvin ~ Adam Alvis ~ Correct

How I build the function in colomnC/remark ?
I believe excel cam solve my problem with its function.

Million thanks brother for your help.

Manggo

 

[Tetra201]

See if this works for you:

=CHOOSE(IF(LEFT(A2,MIN(LEN(A2),LEN(B2)))=LEFT(B2,MIN(LEN(A2),LEN(B2))),LEN(A2)<=LEN(B2),LEFT(A2,MIN(LEN(A2),LEN(B2)))< LEFT(B2,MIN(LEN(A2),LEN(B2))))+1,"Wrong","Correct")<left(b2,min(len(a2),len(b2))))+1,"wrong","correct")< html=""></left(b2,min(len(a2),len(b2))))+1,"wrong","correct")<>

 

[mikerickson]

You could us the formula =(A1 < B1)
or =(LOWER(A1) < LOWER(B1))

 

[manggo]

See if this works for you:

=CHOOSE(IF(LEFT(A2,MIN(LEN(A2),LEN(B2)))=LEFT(B2,MIN(LEN(A2),LEN(B2))),LEN(A2)<=LEN(B2),LEFT(A2,MIN(LEN(A2),LEN(B2)))< LEFT(B2,MIN(LEN(A2),LEN(B2))))+1,"Wrong","Correct")<left(b2,min(len(a2),len(b2))))+1,"wrong","correct")< html=""></left(b2,min(len(a2),len(b2))))+1,"wrong","correct")<>

Thanks Tetra201.
I paste your function but it doesn't work;
Colomn C remarks all with output WRONG

Would you please take a look the file I put your function:
excel file example2.xlsx - Google Drive
screenshoot : example2.png - Google Drive

Million thanks Tetra201, very appreciate.

Manggo

 

[inactiveuserps07]

@Tetra201, cool solution.

Building upon that, this will also work:

=CHOOSE((A2&REPT(" ",100-LEN(A2)) < B2&REPT(" ",100-LEN(B2)))+1,"Wrong","Correct")<b2&rept(" ",100-len(b2)))+1,"wrong","correct")<="" html=""></b2&rept(">

 

[inactiveuserps07]

@Manggo, you have a couple issues in your sheet:

1. First, you typed in Tetra201's formula wrong; you put MAX where he has MIN.

2. However, you also have leading spaces on all your names in Column B, and this will throw things off.


Try this formula, which is built off Tetra201's formula, but accounts for extra spaces:

=CHOOSE((TRIM(A2)&REPT(" ",100-LEN(A2)) < TRIM(B2)&REPT(" ",100-LEN(B2)))+1,"Wrong","Correct")<trim(b2)&rept(" ",100-len(b2)))+1,"wrong","correct")<="" html=""></trim(b2)&rept(">