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]

At last the MVP notices the trouble and jumps to help
, Thank you so much for joining the debate Mr. Rick,

Your code is just marvelous but now I just got my first catch after sorting through the data. What should I change If I got a POINT item instead of Line e.g. for line item the coordinates are G - 7 etc etc. But for a POINT item the coordinates can be like 28 or G or like G / S - 25 or even B - 25 / 23 etc etc. So in reality the items to be discovered inside the box (luckily the right hand side is ALWAYS a rectangle) are either POINTS, LINES or even FULL RECTANGLES.
I hope I clarified. When I was running your code it was working excellently till it reached the data where the items were POINTS so the SPLIT gave me an error.
Many Regards!

 

[Meesam]

At last the MVP notices the trouble and jumps to help
, Thank you so much for joining the debate Mr. Rick,

Your code is just marvelous but now I just got my first catch after sorting through the data. What should I change If I got a POINT item instead of Line e.g. for line item the coordinates are G - 7 etc etc. But for a POINT item the coordinates can be like 28 or G or like G / S - 25 or even B - 25 / 23 etc etc. So in reality the items to be discovered inside the box (luckily the right hand side is ALWAYS a rectangle) are either POINTS, LINES or even FULL RECTANGLES.
I hope I clarified. When I was running your code it was working excellently till it reached the data where the items were POINTS so the SPLIT gave me an error.
Many Regards!

 

[Rick Rothstein]

At last the MVP notices the trouble and jumps to help
, Thank you so much for joining the debate Mr. Rick,

Your code is just marvelous but now I just got my first catch after sorting through the data. What should I change If I got a POINT item instead of Line e.g. for line item the coordinates are G - 7 etc etc. But for a POINT item the coordinates can be like 28 or G or like G / S - 25 or even B - 25 / 23 etc etc. So in reality the items to be discovered inside the box (luckily the right hand side is ALWAYS a rectangle) are either POINTS, LINES or even FULL RECTANGLES.
I hope I clarified. When I was running your code it was working excellently till it reached the data where the items were POINTS so the SPLIT gave me an error.
Many Regards!

First, an admonishment.... I have quickly reviewed this thread and I have to say... you have made it most difficult for the volunteers here to help you. For example, you did not tells us that you were actually looking for some kind of shape (not identified as a line until later on) within a rectangle until Message #10; you did not mention you were looking for a VB solution until Message #17; you did not tell us the letters could have dots followed by numbers until Message #25 and you did not explain what that dot followed by a number attached to the letter meant until Message #27; now you are telling us that your "lines" could be "points" instead. Everyone of these revelations were important and changed the approach needed to solve the problem you presented to us. For future questions, you must keep in mind one general fact... we know absolutely nothing about your data... we do not know how it is laid out, what it means nor how it needs to be manipulated except for what you specifically tell us... if you do not tell us, you cannot expect us to guess it... as obvious as everything is to you, it is totally unknown to us!

Okay, admonishment time is over. Now, about your latest revelation... I don't understand its notation. To me, a point has an x,y coordinate... if you want to consider a point to be a degenerate line, then it has two x,y coordinates both of which are the same... but that is not what you are telling us for how points are designated in your system. For a line, say B-10/E-30, one end of the line is B-10 and the other end of the line is E-30 (where for calculation purposes, I supposed the letters were the y-axis value and the number was the x-axis value making up the coordinate). Now you are saying a "point" is described like this... G/S-25... or like this... B-25/23. I don't understand how this defined a point in a two-dimensional plane. For G/S-25... the S-25 describes and x,y coordinate, no problem, but what coordinate does G represent??? Can you provide more details on how you describe points in your system?

 

[Meesam]

Hello Rick, Late response due to time - zone difference !
First of all I had already in one of my thread clarified the reason for making the problem difficult "Intentionally". Actually I'm not making it difficult rather releasing information slowly so that it can be absorbed in proper way. Maybe I was wrong in one assumption that after first responses I should've given whole data but not before getting response. You may have noticed that I posted another thread with heading "Parsing gridline data and comparing with other data to get grid ranges" and nobody even replied to it which was quite disappointing as I really wanted the solution. Secondly I had even tried many other excel sites and posted the thread in same context but nobody dared to reply. The only solution that I thought was feasible was to break the problem into several smaller pieces so that people may not be intimidated to respond and voila I really got the result. I'm extremely thankful to whoever contributed to this thread so far and made me reach where I am now (almost 80% solved).
Now coming back to the problem, my overall problem is related to DRAWINGS as is obvious from whole discussion. Actually we have lots of references in our Invoice documents, which point to various areas (COLUMNS, BEAMS, SLABS etc) in various drawings. As you may not that a column can be only represented by ONE point and a beam by two and slab also can be represented by two points (rectangular ones). I got a hectic long list of these grid-line references (6,800 total) from which I have to find out which grid-line correspond to which drawing (drawing table is separate). The guys who initially put the data in grid-line list are not that much experienced or expert guys and they've just logged the data intuitively and not on a set pattern. Overall data is accumulated over a period of four years.
Following are examples are of actual grid-line data.

GL 22.5-23 / R.5-S

GL 22.5-23.5 / R.5-T

GL 22.5 / Q.5, 22.5 / R,23/ R,23.5/ R and 22.5/ R.5

GL 27.5 / S

GL 27-27.5 / R.5-S

GL 26-26.5 / R-S

GL 24-24.5 / R.5-T & GL 25.5-26 / R.5-T

GL 23.5-24 / R.5 -T

<tbody>
</tbody>

The conventions are same as I explained. To make the whole stuff much simpler I've attached the actual grid-line reference example.
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
Regards

 

[Rick Rothstein]

That picture helped immensely as did this statement... "they've just logged the data intuitively". I note my interpretation of which axis contained the letters was incorrect, but it does not effect that calculation within my function as all the coordinates are relative to each other within the same assumed coordinate system. I may correct my assumed coordinate system in my next function (even though it is unnecessary); however, I need to you to confirm two of my "guesses" as what the "intuition" was for the following data from your list of real data entries.

Should this...

GL 22.5 - 23.5 / R.5 - T

be interpretted as meaning this...

GL 22.5 - GL 23.5 / R.5 - T.0

where my guesses are highlighted in red? If the answer is yes, then almost everything makes sense. The only other thing I need clarified are these, which I assume are basically the same except that one used the word "and" and the other used an ampersand in place of using the word "and"...

GL 22.5 / Q.5, 22.5 / R,23/ R,23.5/ R and 22.5/ R.5

GL 24-24.5 / R.5-T & GL 25.5-26 / R.5-T

Why are these grouped together in a series and, more importantly, do I need to have my function account for this grouping as something that will be passed into the function as a single argument or will you be passing each individual set of coordinates into the function separately?

 

[Meesam]

Actually GL is nothing relevant to actual data and means only one thing that is "Grid-Line" so we can just remove or filter GL and it's quite easy to do so. So please discard GL. Yeah "and" and "&" were also like junk additions to data and should be discarded. More number of coordinates is what PAINS me the most !!! This single column is part of big table in which we have different areas and inspection references and lot of other data. So against each reference data or area we have certain number of items. Just to give an example: for a single invoice we can put together grid-line reference of two (02) BEAMS, three (03) COLUMNS and so on and all these are put in ONE cell due to the fact that they belong to certain cell. But it's quite obvious that we can separate them using further SPLIT statements or even "Text to Columns" toolbar. That will work hopefully. Also ".0" addition to "T" in "T.0" is excellent idea though in actual data T.0 will always be referred to as "T" only.
I hope it's clear now !

 

[Rick Rothstein]

Actually GL is nothing relevant to actual data and means only one thing that is "Grid-Line" so we can just remove or filter GL and it's quite easy to do so. So please discard GL. Yeah "and" and "&" were also like junk additions to data and should be discarded. More number of coordinates is what PAINS me the most !!! This single column is part of big table in which we have different areas and inspection references and lot of other data. So against each reference data or area we have certain number of items. Just to give an example: for a single invoice we can put together grid-line reference of two (02) BEAMS, three (03) COLUMNS and so on and all these are put in ONE cell due to the fact that they belong to certain cell. But it's quite obvious that we can separate them using further SPLIT statements or even "Text to Columns" toolbar. That will work hopefully. Also ".0" addition to "T" in "T.0" is excellent idea though in actual data T.0 will always be referred to as "T" only.
I hope it's clear now !

Sorry, just a couple of more questions...

1) In the picture whose link you posted, there is this for the slab... 2.8-3.8/Z.8-B.4... can I assume Z is short for "Zero", meaning the origin?

2) In Message #32, you said you could have data that looked like this... G/S-25 or even B-25/23... how are these to be interpretted? These designations seem inconsistent with either the picture whose link you posted or the previous method of designating shapes that you posted in previous messages. How can you have a single letter or single number on either side of the slash character? If you could provide a picture showing the shape aligned (in near scale) to the axis like the linked picture showed, that would be most helpful.

3) I need a clarification about what you posted in Message #15. You said this... "I have to check whether or not first value of C - 20 / D - 20 falls within rectangle B - 10 / E - 30 !!", but that method of describing lines and rectangles (slabs?) differs from what is shown in the picture whose link you posted. Are there two methods of showing rectangles (if yes, that might complicate things a little)? What I mean is, what you posted in Message #15 would suggest that the slab shown in the picture whose link you posted could be designated as Z.8-2.8/B.4-3.8, but the picture shows it as 2.8-3.8/Z.8-B.4... two totally different ways to specify the corners of the rectangle... are both correct?

 

[Meesam]

Ok I'm really sorry for previous references which I just made-up to make the case. Picture here is REALLY important as it shows the ACTUAL way data is presented in the table. For "Z" that means "ZERO" as that line is showing alphabets (X-axis). But even without Z, we can use 0 at origin so on X-axis first alphabet will be "A" after 0 and on y-axis first number will be "1" after 0. This final notation convention (as in the picture) takes precedence over anything else.

 

[XOR LX]

“I'm not making it difficult rather releasing information slowly so that it can be absorbed in proper way”</SPAN></SPAN>

“Yeah "and" and "&" were also like junk additions to data and should be discarded.”</SPAN></SPAN>

“Ok I'm really sorry for previous references which I just made-up to make the case.”</SPAN></SPAN>

I see things haven't improved much since I left. I'm only glad I pulled out when I did - Mr. Rothstein, you are far more patient a man than I!

 

[Meesam]

d4 d6, you got nice sense of humor and I like that