Nested Information

[jomiscli]

Hey guys.

I am currently working on a crafting recipe helper for a game I play. The general idea is to have a drop down menu where you can select the recipe you want to craft, select how many you want to craft, and then get a total of all the raw uncrafted materials you would need to make said query. All I have right now is a table of all the basic raw materials seperated by type and rarity. Heres an example:

Common Stone	Rare Stone	Common Wood	Rare Wood	Common Metal	Rare Metal	Common Crafted	Rare Crafted
Quartz	Ruby	Oak	Beech	Bronze	Platinum	Oak Panel	Beech Shield Panel
Granite	Diamond	Maple	Ash	Steel	Gold	Bronze Handle	Gold Handle

There are quite a few more in my table but this is the gist. The issue I ran into was when rarer items needed common items to be crafted. I can't figure out how to give the Gold Handle its value by drawing a value from the Bronze Handle.

Bronze Handle
Uncrafted Items	Amount
Bronze	1
Oak	1

Gold Handle
Uncrafted Items	Amount	Crafted Items	Amount
Gold	1	Bronze Handle	1
Ash	1

The further down the rarity tree you go, you end up needing 3-4 recipes and basic materials to craft the next item. I can't find a good way to do this. Would making a small table like above be a good way to store the material value of the item while also being able to use it with other recipes to add its value to it? If so how would I use tables to use that info in such a way? I thought I was doing ok but then my math keep being wrong and I can't seem to find a decent way to set my tables up to handle info nesting like that.

I hope this makes sense, I tried to be as clear as possible without making to long of a post.

Any help is appreciated!

 

[Herakles]

Hey guys.

I am currently working on a crafting recipe helper for a game I play. The general idea is to have a drop down menu where you can select the recipe you want to craft, select how many you want to craft, and then get a total of all the raw uncrafted materials you would need to make said query. All I have right now is a table of all the basic raw materials seperated by type and rarity. Heres an example:

Common Stone	Rare Stone	Common Wood	Rare Wood	Common Metal	Rare Metal	Common Crafted	Rare Crafted
Quartz	Ruby	Oak	Beech	Bronze	Platinum	Oak Panel	Beech Shield Panel
Granite	Diamond	Maple	Ash	Steel	Gold	Bronze Handle	Gold Handle

There are quite a few more in my table but this is the gist. The issue I ran into was when rarer items needed common items to be crafted. I can't figure out how to give the Gold Handle its value by drawing a value from the Bronze Handle.

Bronze Handle
Uncrafted Items	Amount
Bronze	1
Oak	1

Gold Handle
Uncrafted Items	Amount	Crafted Items	Amount
Gold	1	Bronze Handle	1
Ash	1

The further down the rarity tree you go, you end up needing 3-4 recipes and basic materials to craft the next item. I can't find a good way to do this. Would making a small table like above be a good way to store the material value of the item while also being able to use it with other recipes to add its value to it? If so how would I use tables to use that info in such a way? I thought I was doing ok but then my math keep being wrong and I can't seem to find a decent way to set my tables up to handle info nesting like that.

I hope this makes sense, I tried to be as clear as possible without making to long of a post.

Any help is appreciated!

What it seems that you have here is Manufacturing Resource Planning (MRP) where items (recipies it seems in your case) are made up of individual parts and assemblies and these assemblies can be made up other assemblies and or individual parts. To set up MRP you will need to compile a Bill of Materials. I can help you do this.

Does this seem about right?

What version of Excel are you using? Can you please update your profile with this information.

There is another ongoing Post at the moment that is getting quite complicated because the tree is huge but this does not look as complicated.

 

[jomiscli]

Hello. I have updated my profile information. I am currently using Excel 365.

Yes you are correct.

I pretty much changed how I was doing everything last night. I compiled all the info I had into one large table with columns for the crafted items, and then rows for the resource items. Where a crafted item and a resource item met I would enter the amount needed to make the item in that column and then I used =IF(ISNUMBER(Table2[Bronze Handle]), Table2[Bronze Handle], "") and =IF(ISNUMBER(Table2[Bronze Handle]), Table2[Resource Row Column], ""). These basically give me the amount of a resource and the name of that resource as a return. This doesn't currently work with items that require other crafted items because I can't figure out how to extract the value of the previously crafted item and still keep the amount of that item thats needed.

Thank you for your response!!

 

[Herakles]

Hello. I have updated my profile information. I am currently using Excel 365.

Yes you are correct.

I pretty much changed how I was doing everything last night. I compiled all the info I had into one large table with columns for the crafted items, and then rows for the resource items. Where a crafted item and a resource item met I would enter the amount needed to make the item in that column and then I used =IF(ISNUMBER(Table2[Bronze Handle]), Table2[Bronze Handle], "") and =IF(ISNUMBER(Table2[Bronze Handle]), Table2[Resource Row Column], ""). These basically give me the amount of a resource and the name of that resource as a return. This doesn't currently work with items that require other crafted items because I can't figure out how to extract the value of the previously crafted item and still keep the amount of that item thats needed.

Thank you for your response!!

It is good that you are using 365 as it has some very useful functions.

Can you post some data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I've found a Bill Of Materials structure like this is necessary and then some VBA code with recursive calling to drill down to as many levels as the BOM demands. There is a limit ro
recursive calls in Excel VBA though but this looks quite simple and won't reach that number.

Nested Information.xlsm
	A	B	C	D	E
1	Parent	Parent Type	Child	Child Type	Quantity
2	Product 1	Product	Assembly 1	Assembly	2
3	Product 1	Product	Assembly 3	Assembly	1
4	Assembly 1	Assembly	Part 1	Part	4
5	Assembly 1	Assembly	Part 2	Part	1
6	Assembly 1	Assembly	Assembly 2	Assembly	6
7	Assembly 2	Assembly	Part 4	Part	1
8	Assembly 2	Assembly	Part 5	Part	3
9	Assembly 3	Assembly	Part 6	Part	1
10	Assembly 3	Assembly	Part 7	Part	1
11	Assembly 3	Assembly	Part 8	Part	1
12	Assembly 3	Assembly	Part 9	Part	2
13	Product 2	Product	Assembly 2	Assembly	1
14	Product 2	Product	Assembly 4	Assembly	5
15	Product 2	Product	Part 10	Part	2
16	Assembly 4	Assembly	Part 4	Part	4
17	Assembly 4	Assembly	Part 8	Part	1
BOM (2)

 

[jomiscli]

Resources	Bronze Handle	Gold Handle	------------------	------------------	------------------	------------------	---------------->	insert formula 1	insert formula 2
Bronze	1							1	Bronze
Oak	1							1	Oak
|								|	|
|								|	|
Gold		1						|	|
Ash		1						|	|
|								|	|
\/								|	|
Bronze Handle		1						\/	\/

I apologize, I'll have to look into the XL2BB Addin soon! For now this is all I got I should be able to check it out later on today.

The table above is really a direct representation of what I have so far. I had to scrap what I was doing before to get here.

=IF(ISNUMBER(Table2[Bronze Handle]), Table2[Bronze Handle], "") this goes in formula 1

=IF(ISNUMBER(Table2[Bronze Handle]), Table2[Resources], "") this goes into formula 2

The bold and italic Bronze Handle would be linked to a control form with either a drop down menu, list, or something with all of the header columns as an option. When you select a specific column header (which are all of the crafted items in the game) it will automatically show the resource value of that item.

Obviously I plan to make a much neater sheet that would eventually show all this information with the ability to increase how many you want to build and eventually to multiple items at once.

I don't have anything for returning values for already crafted items in the resource column. I am not sure if those will even work there but last night it seemed like a good idea and then I just couldn't get anything out of it so I had to scrap what I was using as it wasn't making sense anymore.

 

[jomiscli]

Yeah I dont know I am completely lost here. Thanks for the help.