# How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

1. ## How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

I need some help to find and match patterns of strings in a column and fetch data from the adjacent column. I've attached a sample workbook with my sample data.

How can I find the appropriate matching pattern and fetch and fill data from the adjacent column from my source table to destination? I tried the string functions available and used SEARCH function to match the pattern and check whether it is available. However, when the pattern is found, how can I fetch the adjacent column ?

My attempt to code a formula using SUBSTITUTE, MID and SEARCH functions made me stumble at a Frankenstein monster
Below is the monster formula I wrote - it works and returns 1 when the pattern is found.

Formula:
`Please Login or Register  to view this content.`
I need to return the matching pattern that is found. And with it the corresponding adjacent cell's value. Can anyone help me ?

2. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Bumping due to no response..

3. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

I am not really sure how you are finding a match pattern, but I tried this...
K3=A3&B3&C3
L3=F3&G3&H3
M3=MATCH(K3,L3:L11,0)
copied down, and I got a match in row 7?

4. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Hi Ford,

These formulas doesn't suit my exact need. I want to get the col D values from the source table into my destination. But, I only have the pattern of data (of col C in the source) in my destination table, which I need to match. You can refer to the pictorial representation I've made using arrows in the attachment in the initial post.

5. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Can anyone help me with this requirement?

6. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Hi,

I cannot understand your layout or which are your desired results. In particular, I cannot understand how you generate the values for Possible Combinations for Match.

Regards

7. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

There are two ways I can think to do this. One is with formulas and the other is with Regex in VBA. I tried the formula way. I don't know what your full data set looks like, but this method seems to work for the examples provided.
General strategy:
1. Generate list of possible matches
2. Try all the matches and sort to put the ones that worked on top

Hope this helps!

8. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Hi k64,

I really don't get it how you are able to work this out, the list of possible combinations are (as attached). Can u please explain how this works ?

9. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

1. Create a 2 column list with patterns in column 1 and possible matches in column 2 (patterns will be repeated).
2. Then, in column 3, you try to match all of the possible matches. Only one will work per pattern, or else you have an impossible problem.
3. Sort by the 3rd column, to put those that did match on top.
4. On your destination data, lookup each pattern in your table and return the column 3 number. This is the matching row for your source data.
5. Use that number to look up your value from your source data.

10. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Originally Posted by k64
1. Create a 2 column list with patterns in column 1 and possible matches in column 2 (patterns will be repeated).
2. Then, in column 3, you try to match all of the possible matches. Only one will work per pattern, or else you have an impossible problem.
3. Sort by the 3rd column, to put those that did match on top.
4. On your destination data, lookup each pattern in your table and return the column 3 number. This is the matching row for your source data.
5. Use that number to look up your value from your source data.
K64's solution, works fine. Thanks a lot, k64..

However, instead of creating patterns and then sorting them, can't we just create the possible patterns alone to check ? This way the set of possible matches reduces get reduced. For example, for pattern E(C, M, H)1P36B, can't we create the matches as EC1P36B, EM1P36B, EH1P36B and check these alone ?

(For more examples, refer the screenshot I've shown in post #8).

Can a more simpler solution be coded ?

11. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Are there any other simpler solutions? As in my example, can the possible patterns be just generated and matched against the source table?

12. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

I'm not sure what you mean. In my example, I generate possible matches and try them against the source table. Are you asking if I can avoid generating E1P36B as a possible match? That might be possible. Otherwise, without using macros, you can't get much simpler than this. You'll always have to generate possible matches and try them to see which ones work.

13. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Originally Posted by k64
Are you asking if I can avoid generating E1P36B as a possible match? That might be possible.
To answer your question.. Yes, I want to avoid generating unused matches.

For example, in column C, the valid matches for the pattern E(C,M,H)1P36B can only be EC1P36B, EM1P36B and EH1P36B. Then, to fill out column D from Source table, we can use INDEX and MATCH functions, by taking a combination of column A, B and C, right? So, one of these 3 valid matches would definitely match with the entire source table row.

In this example, a combination of values A80DS2V090B16, 4SCU13LE136P and EM1P36B will return the valid number 5721277 from column D, other 2 combinations won't have a match.

I believe the below INDEX formula can be used to retrieve contents of column D :
Formula:
`Please Login or Register  to view this content.`

14. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Where are you putting that formula? It doesn't work for me when I put it in Pattern_Match!D3. I modified my formula to only generate possible matches.

15. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Originally Posted by k64
Where are you putting that formula? It doesn't work for me when I put it in Pattern_Match!D3. I modified my formula to only generate possible matches.
Hi k64,

Thanks for the solution. The formula you've coded needs to return column the values of col D from source table in Pattern_Match sheet to col I in the Destination table in the same sheet. This is working fine as required.

In the Matching sheet, is there any limit on how many times we need to generate the possible patterns of column B? Because, I find that your formula in col D generates all possible patterns (for this particular set) till col F. So I can limit my pattern set generated in the column range D to F. So, consequently the possible matches in column J are still reduced.

Your solution works perfectly, its brilliant !!. Can you please explain how this works in detail. I want you to explain how you populate the range that's colored in the Matching sheet as in the attached workbook. I could not understand the logic you've mentioned as steps in your post #9.

16. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

Glad it works . Here is the logic.
1. I need to generate possible matches.
1a. I paste the patterns into Column B of Matching. Then I search for parentheses (I assume there will be at most one set).
1b. If I don't find them, then I add a * to the end. Example RCFL-A*3821->RCFL-A*3821* so that it will match RCFL-A(anything)3821(anything).
1c. If it does have parentheses, I take what's inside them and put it in column C.
1d. I then take the rest of the pattern and insert the 1st, 3rd, 5th, etc characters from column C. (I assume that it will always have the pattern (?,?,?), ie that I won't encounter (??,?) or (? ,? )).
1e. I extend my formula as far as needed. In this case I need 3 columns since the most letters inside parentheses is 3 (C,M,H). If there was one that had (C,L,M,P,T) then I would need 5 columns.
2. Now I need to check the possible matches
2a. I cycle through the table I created to make a two-column table in columns I and J.
2b. I copy and paste the values into L and M
2c. I remove duplicates (since I generated some duplicates for patterns with <3 letters in the parentheses)
2d. I try all the matches in column N
2e. I sort by N to put the ones that worked on top.
3. Now I need to connect my source and destination data
3a. For each pattern, I find which possible match worked. Since I sorted, it will be the first instance of that pattern.
3b. Now I find which row of my source data was the match (this is the value in N)
3c. I look in that row of my source data in column D and return the result

Hope this helps to make things clear

17. ## Re: How to Find a Matching Pattern and Retrieve Data from the Adjacent Column

deleted, posted in error

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1