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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
SearchItem IDDescriptionUoMComponent ID
REC000001REC000001PIZZA 4 CHEESE GIGI RECPCSSUB000079
SUB000079<ArrayREC000001PIZZA 4 CHEESE GIGI RECPCSING000401
SUB000076REC000001PIZZA 4 CHEESE GIGI RECPCSING000391
SUB000005REC000001PIZZA 4 CHEESE GIGI RECPCSING000785
REC000001PIZZA 4 CHEESE GIGI RECPCSSUB000076
SUB000005SUB GARLIC OILLTRING000188
SUB000005SUB GARLIC OILLTRING000024
SUB000076SUB PIZZA DOUGHPCSING000208
SUB000076SUB PIZZA DOUGHPCSING000372
SUB000076SUB PIZZA DOUGHPCSING000188
SUB000076SUB PIZZA DOUGHPCSING000691
SUB000076SUB PIZZA DOUGHPCSING000402
SUB000079SUB CREAM BASEKGING000714
SUB000079SUB CREAM BASEKGING000691
SUB000079SUB CREAM BASEKGSUB000005
SUB000079SUB CREAM BASEKGING000397
SUB000079SUB CREAM BASEKGING000257
SUB000079SUB CREAM BASEKGING000580
MEN000348BBQ CHICKEN PIZZA RECPCSSUB000076
MEN000348BBQ CHICKEN PIZZA RECPCSSUB000079
MEN000348BBQ CHICKEN PIZZA RECPCSING000061
MEN000348BBQ CHICKEN PIZZA RECPCSING000188
MEN000348BBQ CHICKEN PIZZA RECPCSING000785
MEN000348BBQ CHICKEN PIZZA RECPCSING000477
MEN000348BBQ CHICKEN PIZZA RECPCSING000067
MEN000348BBQ CHICKEN PIZZA RECPCSING000397
MEN000348BBQ CHICKEN PIZZA RECPCSPAC000059
MEN000348BBQ CHICKEN PIZZA RECPCSPAC000054
MEN000348BBQ CHICKEN PIZZA RECPCSPAC000055
MEN000348BBQ CHICKEN PIZZA RECPCSPAC000181
SUB000005SUB GARLIC OILLTRING000188
SUB000005SUB GARLIC OILLTRING000024
SUB000076SUB PIZZA DOUGHPCSING000208
SUB000076SUB PIZZA DOUGHPCSING000372
SUB000076SUB PIZZA DOUGHPCSING000188
SUB000076SUB PIZZA DOUGHPCSING000691
SUB000076SUB PIZZA DOUGHPCSING000402
SUB000079SUB CREAM BASEKGING000714
SUB000079SUB CREAM BASEKGING000691
SUB000079SUB CREAM BASEKGSUB000005
SUB000079SUB CREAM BASEKGING000397
SUB000079SUB CREAM BASEKGING000257
SUB000079SUB CREAM BASEKGING000580
 
Upvote 0
Anyone have any ideas or can point me in the right direction?
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEF
1SearchItem IDDescriptionUoMComponent ID
2REC000001REC000001PIZZA 4 CHEESE GIGI RECPCSSUB000079
3SUB000079<ArrayREC000001PIZZA 4 CHEESE GIGI RECPCSING000401
4SUB000076REC000001PIZZA 4 CHEESE GIGI RECPCSING000391
5SUB000005REC000001PIZZA 4 CHEESE GIGI RECPCSING000785
6REC000001PIZZA 4 CHEESE GIGI RECPCSSUB000076
7SUB000005SUB GARLIC OILLTRING000188
8SUB000005SUB GARLIC OILLTRING000024
9SUB000076SUB PIZZA DOUGHPCSING000208
10SUB000076SUB PIZZA DOUGHPCSING000372
11SUB000076SUB PIZZA DOUGHPCSING000188
12SUB000076SUB PIZZA DOUGHPCSING000691
13SUB000076SUB PIZZA DOUGHPCSING000402
14SUB000079SUB CREAM BASEKGING000714
15SUB000079SUB CREAM BASEKGING000691
16SUB000079SUB CREAM BASEKGSUB000005
17SUB000079SUB CREAM BASEKGING000397
18SUB000079SUB CREAM BASEKGING000257
19SUB000079SUB CREAM BASEKGING000580
20MEN000348BBQ CHICKEN PIZZA RECPCSSUB000076
21MEN000348BBQ CHICKEN PIZZA RECPCSSUB000079
22MEN000348BBQ CHICKEN PIZZA RECPCSING000061
23MEN000348BBQ CHICKEN PIZZA RECPCSING000188
24MEN000348BBQ CHICKEN PIZZA RECPCSING000785
25MEN000348BBQ CHICKEN PIZZA RECPCSING000477
26MEN000348BBQ CHICKEN PIZZA RECPCSING000067
27MEN000348BBQ CHICKEN PIZZA RECPCSING000397
28MEN000348BBQ CHICKEN PIZZA RECPCSPAC000059
29MEN000348BBQ CHICKEN PIZZA RECPCSPAC000054
30MEN000348BBQ CHICKEN PIZZA RECPCSPAC000055
31MEN000348BBQ CHICKEN PIZZA RECPCSPAC000181
32SUB000005SUB GARLIC OILLTRING000188
33SUB000005SUB GARLIC OILLTRING000024
34SUB000076SUB PIZZA DOUGHPCSING000208
35SUB000076SUB PIZZA DOUGHPCSING000372
36SUB000076SUB PIZZA DOUGHPCSING000188
37SUB000076SUB PIZZA DOUGHPCSING000691
38SUB000076SUB PIZZA DOUGHPCSING000402
39SUB000079SUB CREAM BASEKGING000714
40SUB000079SUB CREAM BASEKGING000691
41SUB000079SUB CREAM BASEKGSUB000005
42SUB000079SUB CREAM BASEKGING000397
43SUB000079SUB CREAM BASEKGING000257
44SUB000079SUB CREAM BASEKGING000580
Lists
Cell Formulas
RangeFormula
A3:A5A3=LET(f,UNIQUE(FILTER(F2:F44,(C2:C44=A2)*(LEFT(F2:F44,3)="Sub"))),r,ROWS(f),b,UNIQUE(FILTER(F2:F44,(LEFT(F2:F44,3)="Sub")*(ISNUMBER(MATCH(C2:C44,f,0))))),s,SEQUENCE(r+ROWS(b)),IF(s<=r,INDEX(f,s),INDEX(b,s-r)))
Dynamic array formulas.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Works like a charm! Thank you so much Fluff!
Hi Fluff, just looking at the formula in more detail and realized that its only looking for "Sub" as a string when in fact I would need it to find and list all compenent ID's that start with either Sub or Rec or Men for example.

Would you please help me figure out how to modify to be able to define multiple 3 letter codes?
 
Upvote 0
Could you post some sample data that has those values.
 
Upvote 0
Could you post some sample data that has those values.

With the expected outcome on the left

SearchItem IDDescriptionComponent ID
MEN000445AEF000039SMOKEY BURGER - AEFMEN000180
AEF000039AEF000039SMOKEY BURGER - AEFSUB000471
SUB000470AEF000039SMOKEY BURGER - AEFING000380
MEN000180AEF000039SMOKEY BURGER - AEFING000389
SUB000471AEF000039SMOKEY BURGER - AEFING000384
SUB000382AEF000039SMOKEY BURGER - AEFING000401
SUB000229MEN000180POTATO BUNING000677
MEN000180POTATO BUNING000372
MEN000180POTATO BUNING000691
MEN000180POTATO BUNING000783
MEN000180POTATO BUNING000669
MEN000180POTATO BUNING000673
MEN000180POTATO BUNING000652
MEN000180POTATO BUNING000303
MEN000180POTATO BUNING000380
MEN000180POTATO BUNING000714
MEN000180POTATO BUNSUB000229
MEN000180POTATO BUNING000764
MEN000445SMOKEY BURGERAEF000039
MEN000445SMOKEY BURGERING000035
MEN000445SMOKEY BURGERING000060
MEN000445SMOKEY BURGERSUB000470
MEN000445SMOKEY BURGERPAC000216
MEN000445SMOKEY BURGERPAC000219
SUB000229SUB EGG WASHING000584
SUB000229SUB EGG WASHING000580
SUB000229SUB EGG WASHING000691
SUB000382SUB TARTAR SAUCE BURGERSUB000009
SUB000382SUB TARTAR SAUCE BURGERING000270
SUB000382SUB TARTAR SAUCE BURGERING000275
SUB000382SUB TARTAR SAUCE BURGERING000052
SUB000382SUB TARTAR SAUCE BURGERING000200
SUB000382SUB TARTAR SAUCE BURGERING000421
SUB000470SUB SMOKEY SAUCESUB000382
SUB000470SUB SMOKEY SAUCEING000477
SUB000470SUB SMOKEY SAUCEING000131
SUB000470SUB SMOKEY SAUCEING000200
SUB000471SUB BEEFT PATTY MIX BURGRING000820
SUB000471SUB BEEFT PATTY MIX BURGRING000691
SUB000471SUB BEEFT PATTY MIX BURGRING000257
SUB000471SUB BEEFT PATTY MIX BURGRING000873
 
Upvote 0
Why is SUB000229 in the list of expected results, but not SUB000009?
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,756
Members
448,295
Latest member
Uzair Tahir Khan

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