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

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!

Originally Posted by JDI

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

Originally Posted by JDI

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.

Originally Posted by JDI

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.

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:

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.

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:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

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:

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:

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:

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:

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:

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.