Array formula to look up tables and populate them into a sheet

[joshbjames]

Laziness inspires this project. I am trying to generate a shopping list of ingredients. Since my family is quarantined I want an easy way to create a shopping list for the next month, but I figured this would be great going forward too.

Here is link: Recipes

Sheet "Table of Contents", column1 "Recipe" is the recipe name, column2 "Sheet" is the sheet name that contains the recipe ingredients and column3 "Add" I put an "x" if I want to add the recipe to my shopping list.

Sheet "Meal Schedule", column1 is an array formula that pulls in the recipes chosen (sheet "Table of Contents", column3). "Meal Schedule" column2 "Sheet" looks up the recipe sheet.

Sheet nRecipe, column1 is "QTY", column2 "UOM" is the unit of measure, column3 "Ingredient"

Goal: In sheet "Ingredient List" for each recipe on "Meal Schedule" I would like to pull all the values for all 3 columns ("QTY","UOM", and "Ingredient") from sheets nRecipe.

Example: On Sheet "Table of Contents" I add 2 recipes.

So if Recipe 1 Sheet "Salmon01" is

QTY	UOM	Ingredient
2	oz	Jalapeno Jelly
0.5	tsp	Seasoned Salt
12	oz	Asparagus
1	oz	Crispy onions
0.5	oz	Pecans
2	ea	Salmon Fillets

and Recipe 2 Sheet "Chicken01" is

QTY	UOM	Ingredient
8	oz	Brussels Sprouts
0.5	oz	Smoked Almonds
1	ea	Fuji Apple
1	ea	Shallot
2	tbsp	Roasted Red Pepper Pesto
0.5	oz	Parmesan
2	ea	Chicken Breast

Then in sheet "Ingredient List" I would like

QTY	UOM	Ingredient
8	oz	Brussels Sprouts
0.5	oz	Smoked Almonds
1	ea	Fuji Apple
1	ea	Shallot
2	tbsp	Roasted Red Pepper Pesto
0.5	oz	Parmesan
2	ea	Chicken Breast
2	oz	Jalapeno Jelly
0.5	tsp	Seasoned Salt
12	oz	Asparagus
1	oz	Crispy onions
0.5	oz	Pecans
2	ea	Salmon Fillets

Thanks in advance for any help!

 

[Peter_SSs]

I don't think that you will achieve what you want with formulas, but with a macro "yes".

I would suggest putting the headings only manually into 'Ingedient List' A1:C1 (You will only need to do this once.
Mark the wanted recipes in 'Table of Contents' as you have done then run this macro.

Test in a copy of your workbook.

Sub ShoppingList()
  Dim d As Object
  Dim a As Variant
  Dim c As Range
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Sheets("Table of Contents")
    For Each c In .Range("C2", .Range("C" & .Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      a = Sheets(c.Offset(, -1).Value).UsedRange.Value
      For i = 2 To UBound(a)
        d(a(i, 2) & ";" & a(i, 3)) = d(a(i, 2) & ";" & a(i, 3)) + a(i, 1)
      Next i
    Next c
  End With
  With Sheets("Ingredient List")
    .UsedRange.Offset(1).ClearContents
    With .Range("A2").Resize(d.Count, 2)
      .Value = Application.Transpose(Array(d.Items, d.Keys))
      .Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
      .Resize(, 3).EntireColumn.AutoFit
    End With
  End With
End Sub

 

[joshbjames]

I don't think that you will achieve what you want with formulas, but with a macro "yes".

I would suggest putting the headings only manually into 'Ingedient List' A1:C1 (You will only need to do this once.
Mark the wanted recipes in 'Table of Contents' as you have done then run this macro.

Test in a copy of your workbook.

Sub ShoppingList()
  Dim d As Object
  Dim a As Variant
  Dim c As Range
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Sheets("Table of Contents")
    For Each c In .Range("C2", .Range("C" & .Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      a = Sheets(c.Offset(, -1).Value).UsedRange.Value
      For i = 2 To UBound(a)
        d(a(i, 2) & ";" & a(i, 3)) = d(a(i, 2) & ";" & a(i, 3)) + a(i, 1)
      Next i
    Next c
  End With
  With Sheets("Ingredient List")
    .UsedRange.Offset(1).ClearContents
    With .Range("A2").Resize(d.Count, 2)
      .Value = Application.Transpose(Array(d.Items, d.Keys))
      .Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
      .Resize(, 3).EntireColumn.AutoFit
    End With
  End With
End Sub

Thanks Peter,

I don't have excel, so I will have to convert this for google sheets, but I am excited to give this a try!

 

[Peter_SSs]

I don't have excel, so I will have to convert this for google sheets,

Hmm, posted in 'Excel Questions' forum (I have moved it) with no mention that it is not an Excel question.

Whilst we do allow questions like this, please do not post them in the Excel Questions forum unless their is a direct connection to Excel.