I have some data from two systems where there is no linking field, but I hope to create one. I want to form a link between order number from both systems. They are set up in different formats, but there is one 'common denominator', and that is that each contains the same 4 numbers consecutively. After that, anything goes. Example, there is no way to determine by their character position, there are at least 3 different numbering schemes and there is nothing the same except at some point in each style number, there will be 4 digits in a row that match in every column.

Example:
OrderNo1 OrderNo2
XYZA0199 - 8567 ____ XYZA0199 8567 000
XYXY0 6767 ____ XYZA0199 6767
YXYZA0199 - T0014 -00 ____ Y-199- T0014 0
XX12- A030 -XYZA0199 ____ XYZA0199 X127 A030

Is there a way to examine each result and if any 4 consecutive letter/number combo (with dashes stripped) is a match, to put those 4 characters in a column I can use? My ultimate goal is to be able to use the other data in the table to updated my new data tables, but since the old/new numbering schemes are different, I need some way to match the records.
Appreciate any help, thank you.

You are going to have to think out your rules quite carefully.
You have chosen to highlight what YOU regard as the matching strings.
But based on your given rules, any program would find matches much earlier in the code numbers than your own choices.
It will be quite possible to run a process that looks at every 4 character string in the first number and see which records it matches and record such matches. But as I say, it won't give the answer you have highlighted.

If your 'common denominator' is always off set by a space or by a dash the process will be much easier, is this always the case?

Oh, I see the problem. Yes, you're absolutely right, there are going to be more than one set of matches. One would be an 8 character string which is the project number. The 4 number/letter combos I'm wanting to extract are the actual order number, so I'm not sure what direction to take. I have the 8 character project number already residing in another field, so if there was a way to exclude those 8 successive characters and only look for the remaining 4, it might be possible. This data is coming from 4 systems (upgrades in recent years brought changes to numbering schemes and am trying the match the last of the ones that spread across all 4 systems).

Thank you so much, I sure hadn't thought it all out rightly.

Can you list some examples of each of the 4 systems?

Thanks. The numbering examples are above, it may have been confusing, I used the ____ underscore to separate the 2 numbers because I couldn't get the spacing to look like columns when I posted it. Anyway, unfortunately there is nothing to grab ahold of, such as dashes or spaces, so I should probably just continue the manual reconciliations till I can get all the old data out of there. Any ideas are welcome, thanks so much.

Cathy,

You said the matching orders would have the same 4 consecutive numbers, but in your example you showed some could have consecutive letters. Just for clarification, did you mean the orders would have a string of 4 matching characters (numbers AND letters) rather than just 4 matching numbers?

Yes, you have the right understanding, the characters could be either, thank you and sorry for the confusion.

Cathy,

I started to write a little function to try to automate what you were trying to do, but i dont think it will be much help.

Just comparing 'XX12A030XYZA0199' to all the others you listed gave 26 separate matches.

To explain the high number:
'XX12A030XYZA0199' has the same 4 characters somewhere in:

XYZA01996767 5 times (0199,A019,ZA01,YZA0,XYZA)
XYZA01998567 5 times (0199,A019,ZA01,YZA0,XYZA)
XYZA01998567000 5 times
XYZA0199X127A030 6 times
YXYZA0199T001400 5 times

Like peter said, to do this automatically you will have to think out your rules very carefully. Since you are able to do this 'manually', there is certainly a way to programmatically do this, you just need to determine exactly what steps YOU go through in order to determine if 2 order numbers are actually the same order.

For example, if the 4 characters are never the first 4 characters of the order number, that would reduce the results above by quite a bit.

Andrew

Thanks Andrew. Unfortunately the manual steps are just me looking at it line by line. First I filter on the project, since it is only one project at a time, but the larger projects may have hundreds of orders. Looks like my best bet will be to keep it up this way till all the old data is closed out. I was trying to automate it so new people who arent' familiar with all the order number changes could do the reconcilations too, but I'll go ahead and finish it out with my manual cut/paste method. Thank you so much, I really appreciate your help and thoughts.