+ Reply to Thread
Results 1 to 19 of 19

Lookup and return rows based on pattern start and pattern end

  1. #1
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Lookup and return rows based on pattern start and pattern end

    Hi all,
    I have a "recipe" which looks like this

    19C82-B
    12
    G
    1B
    H
    ...
    H4


    100C9-B
    6
    G
    2B
    H
    12

    What I would like to do is lookup 19C82-B return the rows until the new pattern 100C9-B starts. Patterns are as follow either 1,2,3 characters followed by a C,D, or R and then some number of characters after. Any thoughts?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    Quote Originally Posted by JDI View Post

    What I would like to do is lookup 19C82-B return the rows until the new pattern 100C9-B starts. Patterns are as follow either 1,2,3 characters followed by a C,D, or R and then some number of characters after. Any thoughts?
    Does the pattern always include "-" and do any of the other pieces of data include "-"?

  3. #3
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Lookup and return rows based on pattern start and pattern end

    Quote Originally Posted by FlameRetired View Post
    Does the pattern always include "-" and do any of the other pieces of data include "-"?
    The pattern always has 1 - but it may have more, for example 12C16-A-G. The only "pattern" i can find is that the first characters are either 123C 12C 1C or 123R 12R 1R or 1D where the numbers are 0-9

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    OK, and the rows between patterns....will any of them have "-"?

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    Operating on some hunches I took a guess at what you might be after.

    See the attached and let us know if this is along the lines of what you have in mind......and by-the-way, the fourth argument in the OFFSET function needs to be worked out, yet. But before investing the time in something that might be already wrong I went ahead and hard coded that argument with a 6. I know that part of the formula will not meet your goal......yet. This file is more of a "proof-of-concept" attempt.

    I've relied heavily on Name Manager. There are two dynamic ranges. One is your raw data in column A (DatSource). The other is the data validation list in column P (DV_List) for your drop-down in E1. The formula.....so far....for generating your list, based upon the drop-down selection, is in column D.

    All occurrences of 'DatSource' replace "$A$1:INDEX($A:$A,MATCH("zzzzzzzz",$A:$A,1))". To do otherwise seemed cruel.

  6. #6
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Lookup and return rows based on pattern start and pattern end

    I've uploaded a small sample. Basically if you scroll through it, you can see where the return should end. They are recipes with some header like:

    12C16-A-G
    :2 B
    :G
    2B
    H1
    H
    G

    Comments

    Begin new recipe 66C43-A-G

    In the example, in the search box (some cell) I would enter 19C82-B and the return would be A1 through A34. With A35 being the new recipe. I don't want to complicate things further but being able to search for something 19C and having it return all the matches would be amazing, but I won't press my luck Thanks so much for the help!!!!

    By the way, the real thing is 300k lines :/
    sample.xlsb
    Last edited by JDI; 10-28-2014 at 12:25 PM.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    OK. Your search box is F1. It is a drop-down with all your recipe names. Click on one of them and the recipe header and list of ingredients appears in column E. All those NA errors can be "whited-out" if you like (Let me know). They helped me to know when the formula in column E was copied down far enough as one of your recipes has @ 71 lines. You might want to keep them for that reason, too.

    Hope this helps.

  8. #8
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Lookup and return rows based on pattern start and pattern end

    This is very very good and impressive! I am very grateful. I have a question, M1:M21 lists all the part numbers but I have 315,000 in my database. It looks like these were copied and pasted? If I can get a list of the part numbers extracted and I copy them into that list, not in any order and change the ranges to M:M will it work? I need to be able to change the M field on a daily basis, its actually located on another sheet.


    Thank you so much for all of the help!
    Last edited by JDI; 10-29-2014 at 02:04 PM.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    Quote Originally Posted by JDI View Post
    This is very very good and impressive! I am very grateful. I have a question, M1:M21 lists all the part numbers but I have 315,000 in my database. It looks like these were copied and pasted? If I can get a list of the part numbers extracted and I copy them into that list, not in any order and change the ranges to M:M will it work? I need to be able to change the M field on a daily basis, its actually located on another sheet.


    Thank you so much for all of the help!
    Glad it’s helped, and thanks for the rep.
    Now I’ll do my best to answer your questions.

    1.) “It looks like these were copied and pasted?” In the last upload that’s a pretty close assessment. I actually extracted them by the same formula I used in column P of my first upload then copy/pasted values. I had counted on “-“ to be a unique identifier of part numbers. I was wrong and opted to paste the values.

    If uniquely identifying those can be done then an extracting formula in column M would be no problem. Off the top of my head I can think of two ways this might be accomplished. One would be to append/prepend a unique character or character string……..”∷:” for example to the part numbers. If you like the extracting formula could include removing those so they don’t show up in the drop-down. The other way might be for you to find a way to have column A reserved for part numbers only, including the “∷:End of data” line in A471, and put each corresponding sub-list in column B. If you don't have much control over how you receive your data those strategies might be a pain. In either event we’ll need a way to uniquely identify the part numbers. I don't think counting on the first character to be a number will work. Two of your parts lead with "H".

    2.) “…….and change the ranges to M:M will it work?” Yes…..with a dynamic range reference to replace the M1:M21 references in column E that can be done.

    I'll wait to hear from you on the unique identifier issue.

    Regards,
    FR
    Last edited by FlameRetired; 10-30-2014 at 02:04 PM. Reason: afterthoughts

  10. #10
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Lookup and return rows based on pattern start and pattern end

    The unique identifier should be a a 1,2 or 3 digit number followed by a C, D or R. There are some old recipes with H or other things that do not below. I should have checked the text more carefully. I will list all the examples I can think of.

    1D1009
    1D1009-NC-G
    1D943-A-G
    1D943-NC
    1D96-G
    2C16-A-G
    12C16-IR-G
    12C16-A
    128C495-A-G
    128C490-IR-G
    1R32-NC
    3R32-NC-G
    5R456-A
    24R23435-A-G
    150RA58-F-G

    Let me know what you think...The array was working to return most of the recipes but not all even if I typed in the exact match for the part number like 128C234-A-G was clearly in my recipe list but when I typed it in, nothing came up.

    Thanks again for working with me.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    I did not find "128C234-A-G" on the file you uploaded. If it is included in an updated file I'm confident that the formula in the updated file here would find it.

    The formula I used banks on a pattern of a number preceding the "-". The lone possible exception to this is "500C-03B945MS". It is the last one at the end of your data sets followed by @71 rows of data that bear no resemblance to the other sets. Is it possible that this is not an actual recipe number?

    If you can verify the lone digit followed by "-" pattern I think this formula could work. If the "-" is preceded by a letter this is likely to be more challenging..........some of your ingredients contain "-".

    Have a look at the example formula in column B. That could be the "extractor" formula you are looking for. However with as many lines as you mentioned you are going to have a very long drop-down. Alphabetizing the list might be a good idea. There is formula for that, too.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    Quote Originally Posted by JDI View Post
    The unique identifier should be a a 1,2 or 3 digit number followed by a C, D or R. There are some old recipes with H or other things that do not below................The array was working to return most of the recipes but not all even if I typed in the exact match for the part number like 128C234-A-G was clearly in my recipe list but when I typed it in, nothing came up
    Since you indicated another sheet I took the liberty of moving the source data for recipes to a back sheet named "Source". It includes the extractor range (in bold) in column C of that page. Here is a brief excerpt:

    Row\Col
    A
    B
    C
    D
    1
    19C82-B 19C82-B
    2
    12
    27C1-B
    3
    G 100C9-B
    4
    6
    10C9-A
    5
    G 10CSF1 PADS
    6
    3
    37C30-A
    7
    H 55C34-E
    8
    3
    66C1-C
    9
    G 9R3-L
    10
    6
    25R1-L
    11
    G 37R12-D
    12
    13
    6CA105-A
    13
    10C56-B
    14
    27C1-B 53C30-NC
    15
    15
    55C141-NC
    16
    H 48R14-NC
    17
    2
    79C76-A
    18
    G HR5R9-02
    19
    2
    HR6R2-02
    20
    H 61CA43-A
    21
    2
    500C-03B945MS
    22
    G :::End of data
    23
    2
    #NUM!


    The "Query" sheet includes a query return (column G in this case) that matches the pattern typed into E1. You can move that query return column to the "Source" page if you prefer.

    Here is a brief example from the "Query" sheet:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    37R12-D d 10CSF1 PADS
    2
    37R12-D 37R12-D
    3
    12
    :::End of data
    4
    G
    #NUM!
    5
    2
    #NUM!
    6
    G
    #NUM!
    7
    2
    #NUM!
    8
    H
    #NUM!
    9
    2
    #NUM!
    10
    G
    #NUM!
    11
    2
    #NUM!


    Type in the pattern search in E1. The query column will auto populate with recipes that match. That in turn auto-populates the drop-down in C1. Make your recipe selection from that list and column B will populate with the ingredients.

    I've relied on Dynamic Named Ranges. They are dynamic so that they can automatically accommodate additions to the recipe source data. The extractor and query ranges are also dynamic and update accordingly.

    On the uploaded sheet I've included a list in column H:I the formulas for those DNRs. It's only a reference to help you unravel the formulas. They can be deleted if you like.

    Should you need to modify the SEARCH criterion in the extractor formula note that it is an array formula that must be confirmed in C1 with Ctrl + Shift + Enter then filled down until ":::End of data" appears. That tells you that you have copied down far enough. The query and results formulas are also array formulas. They shouldn't need to be modified. If you need to extend the output of the results in column B of the "Query" sheet, select the entire range including the additional rows, press the F2 key and confirm the entire new range at once with Ctrl + Shift + Enter.

    Again, be sure that ":::End of data" is the last item in your source list. It is a contrived "recipe" that tells your formulas when to stop looking. Without it you will get REF errors from querying your last recipe.

    Regarding the 128C234-A-G recipe I cannot say. It is not included in the example I have. I feel confident that the extractor will catch it. It matches the criterion of number immediately preceding C, D or R.

    The updated file is attached. I hope this does what you want.
    Last edited by FlameRetired; 11-02-2014 at 07:14 PM. Reason: afterthoughts/typos

  13. #13
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Lookup and return rows based on pattern start and pattern end

    Oh yea, you got 100% right. You are going to contribute much to this forum. It's solved, exactly what I needed. I am extremely grateful for all of your work on this challenging problem.
    Thank you very much!!
    Sincerely,
    JDI

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    Quote Originally Posted by JDI View Post
    Oh yea, you got 100% right. You are going to contribute much to this forum. It's solved, exactly what I needed. I am extremely grateful for all of your work on this challenging problem.
    Thank you very much!!
    Sincerely,
    JDI
    Thank you for your kind words.........they may be pre-mature. You are going to have a lot of array formulas with the data source you are anticipating. That might not be such a problem if you are updating not so often. You may have to time this one with a calendar....lol

    A bigger concern is the OFFSET function that "pulls" the recipes out. It is a volatile function. Each time you select a recipe from the drop-down every formula in your workbook is going to recalculate. I've been racking my mind to come up with a better alternative to OFFSET for that reason. If you find this is taxing resources too much you might consider reposting with a cure for that in mind. I was about to call in the cavalry on this one. In fact I still might.

    Glad this was of some help.
    Last edited by FlameRetired; 11-03-2014 at 09:06 PM.

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    OK. Have found a kinder replacement for the OFFSET formula. Copy this formula, select the entire range from B2 of the Query sheet down, enter into B2 and confirm the entire range with Ctrl + Shift + Enter.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This should help you avoid the chronic re-calcs every time you select a recipe from the drop-down.

    Still looking for an alternative to the array extractor formula.
    Last edited by FlameRetired; 11-03-2014 at 11:35 PM.

  16. #16
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Lookup and return rows based on pattern start and pattern end

    I copied it into the cell, I also tried to copy my total recipe list which is 332k rows long, excel is complaining that is ran out of memory haha.

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    Quote Originally Posted by JDI View Post
    I copied it into the cell, I also tried to copy my total recipe list which is 332k rows long, excel is complaining that is ran out of memory haha.
    I'm not surprised that this taxed resources. Am trying to find a work-around for the extractor formula/range. If it's not necessary to have it, we could remove a huge load. I would surmise that's about 70,000 rows of array formulas.........

    If that can't be done formula-wise then it's probably time for VBA / macros.

    BTW: Did you see the replacer formula for OFFSET in Post #15?

  18. #18
    Registered User
    Join Date
    10-02-2014
    Location
    Sylmar, Ca
    MS-Off Ver
    2011
    Posts
    44

    Re: Lookup and return rows based on pattern start and pattern end

    Quote Originally Posted by FlameRetired View Post
    I'm not surprised that this taxed resources. Am trying to find a work-around for the extractor formula/range. If it's not necessary to have it, we could remove a huge load. I would surmise that's about 70,000 rows of array formulas.........

    If that can't be done formula-wise then it's probably time for VBA / macros.

    BTW: Did you see the replacer formula for OFFSET in Post #15?
    Per our conversation, a new file, not all recipes are there just the fist 100k lines... All the current parts are there. I should be able to type in a cell a partial match and return all parts and recipes corresponding to that search.

    Thanks,
    Sam

    Recipes2.zip

  19. #19
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Lookup and return rows based on pattern start and pattern end

    OK. Unfortunately the resulting file is too large for upload (@ 7+ meg).

    Here are the formulas. The first four go into Name Manager. They are dynamic named ranges that the rest of the formulas use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    DropFiller is used for your Data Validation formula. It makes cell D1 work. The rest are used in the following array entered formulas:

    Be sure that the last row of column A Sheet2 contains ":::End of data".
    Correction: this first one is non-array. Enter this non-array formula in B2 Sheet2 and fill down to B99897.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The rest of these are array formulas:
    Enter this one in F1 Sheet3. Committed with Ctrl + Shift + Enter and fill down far enough to accommodate your longest anticipated query return. I filled down @ 160+ rows.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This next one goes into I1 Sheet3 array entered it only goes in this one cell:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Preselect I2:I100 (probably overkill, but one of your recipes had @ 70 rows). Array commit this entire range all at once with this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For added help (not essential, but might help you to verify your output) this single array formula in say L1 of Sheet2; it will tell you the address of your recipe ingredients on Sheet 2.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Once saved this takes over 1 minute to load/calculate and be ready to work with. That's with only the 100,000 rows in your sample. With 300,000+ rows you indicated I don't know how much longer this will take. "Not Responding" messages flash momentarily on my machine. They go away. Subsequent calculations are taking my machine @20 seconds.

    For your consideration I've taken the liberty of uploading a file of two proposed alternative data layouts. I feel confident that data layout changes like these will enable your sheet to be immensely streamlined.

    Hope this works well.
    Attached Files Attached Files
    Last edited by FlameRetired; 11-16-2014 at 11:46 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. Copy down hypertext cell range pattern & cell reference formula pattern
    By Underexcelling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 03:23 AM
  2. Loop through rows identifying a positive/negative pattern and return value
    By nguyeda in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2013, 04:15 PM
  3. Replies: 6
    Last Post: 08-23-2013, 07:56 AM
  4. Copy formula down rows based on pattern
    By briancb2004 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-05-2007, 04:27 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