As tables & spreadsheets don't deal very well with nested data, it's super common to break up an object into multiple lines when writing to a table or spreadsheet. An example using a cookbook recipe could be a recipe object containing an arrayList / collection of separate steps within it.
Example recipe object list (written in JSON as writing VBA objects is difficult since their names are stored in the modules, and this could apply to other languages like vb.net or C# as well):
{
"Baked Potato": {
"Steps": {
"Wash Potato": {},
"Bake Potato": {}
}
},
"Cheese Quesadilla": {
"Steps": {
"Open Tortilla": {},
"Add Cheese": {},
"Microwave 30 seconds": {}
}
}
}
If I were looping over my "cookbook" list of recipes and printing them to a table, the output would look like this:
| Recipe | Steps |
|---|---|
| Baked Potato | Wash Potato |
| Baked Potato | Bake Potato |
| Cheese Quesadilla | Open Tortilla |
| Cheese Quesadilla | Add Cheese |
| Cheese Quesadilla | Microwave 30 Seconds |
While looping over the object is pretty straightforward, where I'm struggling is how do go about the reverse:
Question: How would I loop over each row in the table, and create a new recipe object only for unique recipes, and adding the correct steps to that recipe's step list? (I'm fine going from object to table, it's looping over a table and creating an object I'm having a harder time thinking of.)
My thoughts so far:
I've been leaning towards using an ArrayList instead of dictionary, as things may be nested multiple levels deep (like having list of ingredients) and a list of objects makes more sense to me mentally than the dictionary which I've struggled to really use yet..
I was thinking I would first need to get a unique list of just the recipe names in the table, then loop over that separate unique recipe name list and within that loop have a nested For Each loop for rows start to rows end, and within that loop a while loop that somehow only adds the nested ingredients while the recipe name of the current line matches the unique list?
RecipeList as ArrayList:
- Baked Potato
- Cheese Quesadilla
Pseudocode:
For Each recipe in RecipeList..
New RecipeObject
For Each row in RecipeTable..
While (currentRow,RecipeColumn) in RecipeTable = current recipe in RecipeList
RecipeObject.Name = (currentRow, recipeColumn)
RecipeObject.Add (currentRow, stepsColumn)
This kind of feels like the right track, but maybe isn't the correct use of a while loop as it would break if the recipe lines weren't grouped together in the table. Maybe just a regular If would work better. I also don't want to create a new object with each table line, just one for each unique recipe but pulling out a separate unique list seems like maybe an added unnecessary and slower loop?
Thanks, and sorry didn't know how to phrase this well enough to find a duplicate question.
vba, vb.net, c# or even java examples all welcome as this is more of a strategy question than a language specific one, so I wasn't sure how to best tag this.