Comparing Alphanumeric Strings

[Meesam]

Hello Everybody,

I got a simple sheet in which I got some columns of data representing certain grid-line i.e. A-25, B-35, C-41 etc and I got another column in which the data is like B-25.5, C-26.5 etc. I want to compare the both columns and know for each item that which item is bigger or smaller (grid-line sense) than the other. e.g. C-26.5 will surely be lesser than C-41. I need a simple algorithm or code to achieve the same. Would really appreciate quick response.

Regards.

 

[Matty]

Hi,

Could you post some example data as well as the expected result?

Matty

 

[Meesam]

Hi Matty,
Data can be in the format as below

Column - A Column - B RESULT (to be achieved)
------------- ---------- -----------------------
C-25 B-35 Smaller (i.e. Column B is SMALLER than A)
A-35 H-28.5 Larger
S-11.5 R-26 Smaller

So on and so forth. Remember I got 6,800 ROWS !

Thanks.

 

[Matty]

I'm not sure I follow the logic here.

Could you explain why you get the results you do?

Matty

 

[Meesam]

Logic is quite simple. If you know bit about graphs then you have on x-axis A,B,C,D...........so on.
On y-axis you got 1,2,3,4,5,6..............so on.
If i pick-up a point anywhere in graph, say C-25 (which clearly means C along x-axis and 25 along y-axis) then I can put that in one single column. In the same way I can put another point say D-30 in another column. Now if i want to compare that which one is ahead or higher (in whatever way you take it) then clearly D is farther right of C and so is 30 lower or higher than 25. I hope I could clarify.

 

[Matty]

Sorry, I got my alphabet order messed up earlier.

I take it that if both share the same alpha value, e.g. C, then C-1 is smaller than C-2?

Matty

 

[Meesam]

Exactly you got my right now. Now kindly help me prepare a nice algorithm or basic code so that I can process all those 6,800 lines. Actually my overall problem is lot more complicated and this part is just the start. Many a times I got items such as C-35.5 / B-23 getting compared to C / A-35 which essentially is comparison of two lines items. But anyways even if I accomplish this simple comparison then the other ones can be worked out.

 

[XOR LX]

But which criterion takes precedence: the x-axis or y-axis value? For example, what is your result for which of D4 and E3 is the 'larger'? 'E' is further along than 'D' (in the sense of +ve x-direction), though 4 is less than 3 (in the sense of +ve y-direction).

Regards

 

[Matty]

Try:

=IF(A2=B2,"Same",IF(LEFT(A2,FIND("-",A2)-1)>LEFT(B2,FIND("-",B2)-1),"Smaller",IF(AND(LEFT(A2,FIND("-",A2)-1)=LEFT(B2,FIND("-",B2)-1),--RIGHT(A2,LEN(A2)-FIND("-",A2))>--RIGHT(B2,LEN(B2)-FIND("-",B2))),"Smaller","Larger")))

Matty

 

[Meesam]

Excellent code matty. Actually in my real problem there is no concept of smaller or larger rather the issue is whether or not a certain range (in Column-A) falls under second range (Column-B) e.g. I got A - 35 in Column and I'm comparing to a value A-10 / B-31 then clearly A - 35 is "covered" (or you can say sort of in between or inside) of range A-10 / B-31. So now I've to extend you algorithm for the same. Another thing is that can you please guide me of how the put all this in VBA as I will be doing most other comparison and data pulling using VBA.