+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    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: copy to clipboard
    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 ?
    Attached Files Attached Files
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  2. #2
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Bumping due to no response..

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    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. #5
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Can anyone help me with this requirement?

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    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
    3. Link your patterns to the working matches.

    Hope this helps!
    Attached Files Attached Files
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  8. #8
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    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 ?
    Attached Images Attached Images

  9. #9
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    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. #10
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Quote Originally Posted by k64 View Post
    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. #11
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    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. #12
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    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. #13
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Quote Originally Posted by k64 View Post
    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: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    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.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Quote Originally Posted by k64 View Post
    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.
    Attached Files Attached Files

  16. #16
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    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. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

    deleted, posted in error
    Last edited by FDibbins; 06-10-2014 at 10:01 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find matching value in one column to copy adjacent cell on same row
    By soradsauce in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-21-2020, 12:40 AM
  2. [SOLVED] Retrieve Data From Multiple Worksheets By Matching Column Headers And Row IDs
    By Ariff_Chowdhury in forum Excel Programming / VBA / Macros
    Replies: 67
    Last Post: 04-20-2014, 03:18 PM
  3. Continue a vertical pattern on an adjacent column
    By gkostrom in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-10-2014, 09:33 PM
  4. Need VBA to find matching data between worksheets & copy adjacent rows.
    By Helen62 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2013, 12:19 AM
  5. [SOLVED] Globally modify non-adjacent worksheet formulas matching a pattern
    By tlafferty in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-10-2012, 02:42 PM

Bookmarks

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