Generating a list of values based on multiple vlookup arrays

AmroK

New Member
Joined
Jan 25, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

Been working on a file all day and got it working perfectly only to have it crash and become really slow when I realized I need to expand my look up array formula's that I've had to create in order to achieve my goal. Summary and sample file provided below. Would really appreciate your help!

Using Office 365 on Windows

I have an extract that contains the following columns: Item ID > Description > Unit of Measure > Component ID

Items refer to "Recipes", and each "Recipe" is made up of multiple "Components"

Each "Component" can be either a "Recipe" or an "Ingredient"

Each Recipe ID starts with REC or SUB and each Ingredient ID starts with ING or PAC

All Recipe's are listed in the same table regardless of whether a recipe is a parent or child recipe.

I am trying to paste an "Item ID" into cell A2 and using a formula (not VBA) in cell A3 generate an array of all the Component ID's of the recipe in cell A2 and the Component ID's of its Component ID's (in the case where those are "Recipes") and the Component ID's of their Component ID's, etc until there are only Ingredient ID's in cell Component ID's.

I am then filtering out all Ingredient ID's (so ID's starting with ING or PAC) and using the final list to filter my table (which contains a dump of all my recipes to showcase all Recipes and Ingredients that relate to a single Item

To solve this manually, I created lots of look up array formulas which I then used a formula to create the list as per the below post (Thanks Fluff) but now my file is too slow and is crashing sometimes.

Anyone willing to share a solution please?
 
Last edited by a moderator:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
So if SUB000229 contained a component that was sub, aef or men, that should also get searched?
 
Upvote 0
So if SUB000229 contained a component that was sub, aef or men, that should also get searched?
Yes. Any sub aef or men items WILL have components and so will need to be searched and added to the list until there are no more (edit: Component ID's that contain those prefixes) to search
 
Upvote 0
In that case unfortunately I cannot help. With the approach I was using you would need to hardcopy the number of times it searches for the values, which would get very long.
 
Upvote 0
In that case unfortunately I cannot help. With the approach I was using you would need to hardcopy the number of times it searches for the values, which would get very long.
I can imagine, thanks for trying Fluff.

Would my only solution be a macro? any idea on how I would achieve this using VBA?
 
Upvote 0
Either that or a number of helper columns.
 
Upvote 0
If you are happy with helper columns try
+Fluff 1.xlsm
ABCDEFGHI
1SearchItem IDDescriptionComponent ID
2MEN000445AEF000039SMOKEY BURGER - AEFMEN000180
3AEF000039AEF000039MEN000180SUB000229SUB123456AEF000039SMOKEY BURGER - AEFSUB000471
4SUB000470SUB000470SUB000471SUB123456SUB987654AEF000039SMOKEY BURGER - AEFING000380
5MEN000180MEN000180SUB000229SUB000009AEF000039SMOKEY BURGER - AEFING000389
6SUB000471SUB000471SUB000009SUB987654AEF000039SMOKEY BURGER - AEFING000384
7SUB000382SUB000382SUB123456AEF000039SMOKEY BURGER - AEFING000401
8SUB000229SUB000382MEN000180POTATO BUNING000677
9SUB000009SUB987654MEN000180POTATO BUNING000372
10SUB123456MEN000180POTATO BUNING000691
11SUB987654MEN000180POTATO BUNING000783
12MEN000180POTATO BUNING000669
13MEN000180POTATO BUNING000673
14MEN000180POTATO BUNING000652
15MEN000180POTATO BUNING000303
16MEN000180POTATO BUNING000380
17MEN000180POTATO BUNING000714
18MEN000180POTATO BUNSUB000229
19MEN000180POTATO BUNING000764
20MEN000445SMOKEY BURGERAEF000039
21MEN000445SMOKEY BURGERING000035
22MEN000445SMOKEY BURGERING000060
23MEN000445SMOKEY BURGERSUB000470
24MEN000445SMOKEY BURGERPAC000216
25MEN000445SMOKEY BURGERPAC000219
26SUB000229SUB EGG WASHING000584
27SUB000229SUB EGG WASHSUB123456
28SUB000229SUB EGG WASHING000691
29SUB000382SUB TARTAR SAUCE BURGERSUB000009
30SUB000382SUB TARTAR SAUCE BURGERING000270
31SUB000382SUB TARTAR SAUCE BURGERING000275
32SUB000382SUB TARTAR SAUCE BURGERSUB123456
33SUB000382SUB TARTAR SAUCE BURGERING000200
34SUB000382SUB TARTAR SAUCE BURGERING000421
35SUB000470SUB SMOKEY SAUCESUB000382
36SUB000470SUB SMOKEY SAUCEING000477
37SUB000470SUB SMOKEY SAUCEING000131
38SUB000470SUB SMOKEY SAUCEING000200
39SUB123456SUB BEEFT PATTY MIX BURGRSUB987654
40SUB000471SUB BEEFT PATTY MIX BURGRING000691
41SUB000471SUB BEEFT PATTY MIX BURGRING000257
42SUB000471SUB BEEFT PATTY MIX BURGRING000873
Lists
Cell Formulas
RangeFormula
A3:A11A3=LET(ra,ROWS(B3#),rb,ROWS(C3#),rc,ROWS(D3#),s,SEQUENCE(ra+rb+rc+ROWS(E3#)),a,IF(s<=ra,INDEX(B3#,s),IF(s<=ra+rb,INDEX(C3#,s-ra),IF(s<=ra+rb+rc,INDEX(D3#,s-ra-rb),INDEX(E3#,s-ra-rb-rc)))),UNIQUE(a))
B3:B7B3=LET(Rng,I2:I44,f,UNIQUE(FILTER(Rng,(G2:G44=A2)*((LEFT(Rng,3)="aef")+(LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men")))),r,ROWS(f),b,UNIQUE(FILTER(Rng,((LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men")+(LEFT(Rng,3)="aef"))*(ISNUMBER(MATCH(G2:G44,f,0))))),s,SEQUENCE(r+ROWS(b)),UNIQUE(IF(s<=r,INDEX(f,s),INDEX(b,s-r))))
C3:C9C3=LET(Rng, I2:I44,f,UNIQUE(FILTER(Rng,(ISNUMBER(MATCH(G2:G44,B3#,0)))*((LEFT(Rng,3)="aef")+(LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men")))),r,ROWS(f),b,UNIQUE(FILTER(Rng,((LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men"))*(ISNUMBER(MATCH(G2:G44,f,0))))),rb,ROWS(b),s,SEQUENCE(r+rb),UNIQUE(IF(s<=r,INDEX(f,s),INDEX(b,s-r))))
D3:D6D3=LET(Rng, I2:I44,f,UNIQUE(FILTER(Rng,(ISNUMBER(MATCH(G2:G44,C3#,0)))*((LEFT(Rng,3)="aef")+(LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men")))),r,ROWS(f),b,UNIQUE(FILTER(Rng,((LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men"))*(ISNUMBER(MATCH(G2:G44,f,0))))),rb,ROWS(b),s,SEQUENCE(r+rb),UNIQUE(IF(s<=r,INDEX(f,s),INDEX(b,s-r))))
E3:E4E3=LET(Rng, I2:I44,f,UNIQUE(FILTER(Rng,(ISNUMBER(MATCH(G2:G44,D3#,0)))*((LEFT(Rng,3)="aef")+(LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men")))),r,ROWS(f),b,UNIQUE(FILTER(Rng,((LEFT(Rng,3)="Sub")+(LEFT(Rng,3)="men"))*(ISNUMBER(MATCH(G2:G44,f,0))))),rb,ROWS(b),s,SEQUENCE(r+rb),UNIQUE(IF(s<=r,INDEX(f,s),INDEX(b,s-r))))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,782
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top