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.

 

[XOR LX]

I'm not sure I understand this. Taking your last example, if I was to complete a rectangle using A10 and B31 as two of my corners, then A35 would not lie within the boundaries of this rectangle. Still, looks like your happy!

 

[Matty]

Thinking about it, we don't need to split the string the do this comparison. The following should also work:

=IF(A2=B2,"Same",IF(A2>B2,"Smaller","Larger"))

I have to go out now. Hopefully someone else can help with your follow up question.

Matty

 

[Meesam]

Hey d4d6, Yeah sorry i was bit incorrect on that. Yeah if the point is A-30 or something then surely it will fall within rectangle. That's exactly what I'm trying to achieve using VBA. Thanks for correction.

 

[XOR LX]

So now I'm even more confused! You say: "I got A - 35 in Column and I'm comparing to a value A-10 / B-31". Does this mean that actually your entries in column B consist of a pair of co-ordinates? And that, as I stated, you are effectively looking to see whether the co-ordinate in column A falls within the rectangle having the co-ordinates in column B as two of its corners?

 

[Meesam]

Yes 100% my friend. In column-A, I may have a value C - 20 / D - 20 which represents a LINE. Now in column-B, I may have B - 10 / E - 30 which represents a rectangle. I have to check whether or not first value of C - 20 / D - 20 falls within rectangle B - 10 / E - 30 !!

 

[XOR LX]

Hi,

Try:

=IF(AND(COLUMN(INDIRECT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND("/",A2)-1),"-","")," ","")))>=COLUMN(INDIRECT(SUBSTITUTE(SUBSTITUTE(LEFT(B2,FIND("/",B2)-1),"-","")," ",""))),COLUMN(INDIRECT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND("/",A2)-1),"-","")," ","")))<=COLUMN(INDIRECT(SUBSTITUTE(SUBSTITUTE(MID(B2,FIND("/",B2)+1,LEN(B2)-FIND("/",B2)+1),"-","")," ",""))),ROW(INDIRECT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND("/",A2)-1),"-","")," ","")))>=ROW(INDIRECT(SUBSTITUTE(SUBSTITUTE(LEFT(B2,FIND("/",B2)-1),"-","")," ",""))),ROW(INDIRECT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND("/",A2)-1),"-","")," ","")))<=ROW(INDIRECT(SUBSTITUTE(SUBSTITUTE(MID(B2,FIND("/",B2)+1,LEN(B2)-FIND("/",B2)+1),"-","")," ","")))),"WITHIN","OUTSIDE")

Regards

 

[Meesam]

My friend......
This formula seems to be far more weird than I expected it to be
Anyways I worked out lot more comprehensible and reasonable formula which is
=IF(LEFT(H2,FIND("/",H2,1)-1)>LEFT(J2,FIND("/",J2,1)-1),"h is larger","J is larger")
But I still got lot more to do. I have datasets in which data is like 25 / G - 13.5 so here this one won't give correct results all the time. And most importantly your formula just didn't work and gave ERROR ! Have you tried it before copying here?
Even after all the solutions I will have to convert all logics to VBA. So still on initial steps towards final solution.
Thanks for your time and help.

 

[XOR LX]

Of course I tried it. I used it on C - 20 / D - 20 in cell A2 and B - 10 / E - 30 in cell B2 and it worked just fine.

Don't tell me you tried it on cells H2 and I2 without changing the references accordingly?!

And the formula you are currently using will not do as you stated was required since it is only checking the x-values, not the y-values.

Regards

 

[Meesam]

My very dear friend If i'm in such an advanced forum I can't imagine making obvious mistake of not changing cell references. Anyways for some odd reason it's giving some error. Yeah the one is posted is for left side and you can see immediately that with little bit of fine tuning it can be directly extended to both sides of "/". Fine let me check yours with some modifications. But keep trying for better options and If you can really push then please help me figure out VBA version. I'm good at VBA but for these formulas either i need equivalent algorithm or a way to easily transfer these to VBA without hassle.

 

[XOR LX]

What type of error are you getting? What are the exact text strings in A2 and B2 on which you are currently testing the formula?

Regards