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.
www.mrexcel.com
Anyone willing to share a solution please?
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.
How to extract and list all values from table range that meet certain criteria
Hi guys, I've spent the last few hours trying to find a solution for this, but no luck. Really hoping someone can help? I have a table that I've generated using a bunch of array look up formulas. The list contains ID's in a table format as per the below. I need to be able to use a formula...
Anyone willing to share a solution please?
Last edited by a moderator:
