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.

 

[Meesam]

These are my strings

G - 35.5 / S - 23

S - 28.8 / R - 24

<colgroup><col span="2" width="72"></colgroup><tbody>
</tbody>

Error is "#REF!" and cells are A2 and B2.

 

[XOR LX]

Ah. Apologies. I presumed the grid references were integer values and therefore did not account for decimals. Will have to rethink and get back to you.

 

[Meesam]

Hey d4d6,
Any updates my friend? I'm also working in parallel but bit lost with my day to day works. Please try to give it a try whenever you get some free time. Actually now I got two big separate workbooks from which I need to do all the comparison work. It's quite a herculean task but what we are doing here is the core of algorithm and overall solution. Thanks for your time and effort !

 

[XOR LX]

Hi,

Try this (on entries in cells A1 and B1) and let me know:

=IF(AND(MEDIAN(--TRIM(MID(A1,FIND("-",A1)+1,FIND("/",A1)-(FIND("-",A1)+1))),--TRIM(MID(B1,FIND("-",B1)+1,FIND("/",B1)-(FIND("-",B1)+1))),--TRIM(MID(B1,FIND(REPT("z",255),SUBSTITUTE(B1,"-",REPT("z",255),2))+1,LEN(B1)-FIND(REPT("z",255),SUBSTITUTE(B1,"-",REPT("z",255),2)))))=--TRIM(MID(A1,FIND("-",A1)+1,FIND("/",A1)-(FIND("-",A1)+1))),MEDIAN(COLUMN(INDIRECT(TRIM(LEFT(A1,FIND("-",A1)-1))&1)),COLUMN(INDIRECT(TRIM(LEFT(B1,FIND("-",B1)-1))&1)),COLUMN(INDIRECT(TRIM(MID(B1,FIND("/",B1)+1,FIND(REPT("z",255),SUBSTITUTE(B1,"-",REPT("z",255),2))-FIND("/",B1)-1))&1)))=COLUMN(INDIRECT(TRIM(LEFT(A1,FIND("-",A1)-1))&1))),"WITHIN","OUTSIDE")

Cheers

 

[Meesam]

Hello d3d4, Thanks for your continued support but alas it still couldn't work. Here is the data that I used to verify it.

And it still gave the "#REF!" error. C.2 - 7 / J - 27.5 E - 13.5 / G.3 - 21 <colgroup><col width="133"><col width="131"></colgroup><tbody> </tbody>

<tbody>
</tbody>

Anyways i'm working on VBA part of it and hopefully will work out something. But still will be in touch with you till final completion as you really helped a lot and put lot of your efforts.

 

[XOR LX]

??

Now you have a decimal point between the letter and the number??? I thought these were 'grid references'? What on earth does "C.2 - 7" mean? I can understand "C - 2.7", but not this!

 

[Meesam]

Sorry my dear friend d3d4, Decimal point between letter and number is in the same sense as decimal point in numbers e.g. G.7 will mean G PLUS 7 points or 70% distance towards H. You know after G comes H but in between there are like 10 divisions. So first division is 0.1 G + 0.1 = G.1. I hope that clarifies.

 

[XOR LX]

Ok, I'm going to have to drop out of this one, unfortunately. Ratio of time input (considerable) versus information received (sporadic, incomplete) beyond my threshold. Best of luck with your VBA solution, though.

Regards

 

[Meesam]

No problem my friend. Apologies if your time was wasted. I will surely work out final solution and will send to you and main credit will still go to you. Thanks for your time and effort. Reason for sporadic information is that I'm releasing less information time by time as overall project is lot more complex. I've posted full project at various sites and didn't receive even single response. So I broke it up into pieces and it worked (evident by so many responses). It really is sort of one of my best and most complicated projects.

 

[Rick Rothstein]

Sorry my dear friend d3d4, Decimal point between letter and number is in the same sense as decimal point in numbers e.g. G.7 will mean G PLUS 7 points or 70% distance towards H. You know after G comes H but in between there are like 10 divisions. So first division is 0.1 G + 0.1 = G.1. I hope that clarifies.

If I did everything correctly, the following function, which can be used as a UDF (User Defined Function) within a worksheet formula, will return True if the line lies within the rectangle and False otherwise...

Function IsInside(LineCoords As String, RectangleCoords As String) As Boolean
  
  Dim Parts() As String
  Dim Line1 As String, Line2 As String, Lx1 As Double, Ly1 As Double, Lx2 As Double, Ly2 As Double
  Dim Rect1 As String, Rect2 As String, Rx1 As Double, Ry1 As Double, Rx2 As Double, Ry2 As Double
  
  '  Separate out the two end coordinates of the Line
  Parts = Split(Replace(LineCoords, " ", ""), "/")
  Line1 = Parts(0)
  Line2 = Parts(1)
  '  Calculate x,y coordinate of the line's start
  '  point replacing letter with its ASCII value
  Parts = Split(Line1, "-")
  Lx1 = Parts(1)
  Parts = Split(Parts(0) & ".0", ".")
  Ly1 = Asc(Parts(0)) + Parts(1)
  '  Calculate x,y coordinate of the line's end
  '  point replacing letter with its ASCII value
  Parts = Split(Line2, "-")
  Lx2 = Parts(1)
  Parts = Split(Parts(0) & ".0", ".")
  Ly2 = Asc(Parts(0)) + Parts(1)
  
  '  Separate out the two end coordinates of the Rectangle
  Parts = Split(Replace(RectangleCoords, " ", ""), "/")
  Rect1 = Parts(0)
  Rect2 = Parts(1)
  '  Calculate x,y coordinate of the rectangle's start
  '  point replacing letter with its ASCII value
  Parts = Split(Rect1, "-")
  Rx1 = Parts(1)
  Parts = Split(Parts(0) & ".0", ".")
  Ry1 = Asc(Parts(0)) + Parts(1)
  '  Calculate x,y coordinate of the rectangle's end
  '  point replacing letter with its ASCII value
  Parts = Split(Rect2, "-")
  Rx2 = Parts(1)
  Parts = Split(Parts(0) & ".0", ".")
  Ry2 = Asc(Parts(0)) + Parts(1)

  '  Calculate whether the end points of the line
  '  are within the end points of the rectangle
  IsInside = Lx1 >= Rx1 And Ly1 >= Ry1 And Lx2 <= Rx2 And Ly2 <= Ry2
  
End Function