Bill of material

[fvdw]

Hello guys,

I have been struggling for a while with my goal to automate a bom level structure in excel. In the appendix you can find 2 columns. In column A you can find level and in column B bom. I only receive column A data, and need to recreate the results in column B. Basically, i am asking you guys to help me with formulas or ways to automate this result.

Thanks in advance

[sandy666]

so if you receive , eg. 2 you need to achieve this?

1.1.1
1.1.2
1.1.3
1.1.4
1.1.5
1.2.1
1.2.2
1.2.3
1.2.4
1.2.5
1.2.6
1.2.7
1.2.8
1.2.9
1.2.10
1.2.11
1.2.12
1.2.13
1.2.14
1.2.15
1.2.16
1.2.17
1.2.18
1.2.19
1.2.20
1.2.21
1.2.22
1.2.23
1.2.24
1.2.25
1.2.26
1.2.27
1.2.28
1.2.29
1.3.1
1.3.2
1.3.3
1.3.4
1.3.5
1.3.6
1.3.7
1.3.8
1.3.9
1.3.10
1.3.11
1.3.12
1.3.13
1.3.14
1.3.15
1.3.16
1.3.17
1.3.18
1.3.19
1.3.20
1.3.21
1.3.22
1.3.23

will be better if you show any example what you really receive and what should you achieve
try to be more descriptive

[fvdw]

Hi Sandy,

Thanks for the quick reply.

I will try and make it clearer. Column A is what i actually receive. Column B is what i want to achieve. I only receive column A. I need a proper formula in column B to get the same results that are in column B of the example file.

Context is:

We need to convert Column A to Column B, otherwise it can't be accepted in our system. It's a bill of material (bom) where the higher levels represent the underlying materials that are needed to build the lower levels.

For instance, level 0 is always the endproduct/final assembly, and you need level 1's to build it. However, level 1 got its own underlying materials (level 2's) that are needed to build it and so on and so forth.

I hope this will make it more understandable. Feel free to ask.

[sandy666]

ok, what you should do if you'll receive: 2,4,5 ?
is there are any criteria or something like that, you are very frugal with important information
I know you know but I don't know what you know and unfortunately my crystal ball is currently being repaired

[fvdw]

Hi Sandy,

I have added a new appendix with arguments that may help understanding and create a formula.

I think i have covered every scenario that may happen.

Some additional information; for every next row, the level can only rise 1 at and never more than 1 at a time.
Though, the level can drop multiple levels in a next row.

Let me know if you have more questions.

Thanks a lot

[sandy666]

ok, got it
I'll try with PQ, if you want formula you'll need to wait for someone else

[windknife]

Try this,

C2

Please Login or Register  to view this content.

copied down.

[AliGW]

Are you still using Excel 2013 or somthing newer?

[fvdw]

Hello everyone that helped me.

I would like to thank all of you for your efforts, i greatly appreciate it. The answer of windknife seems to work well. Is there any common way to thank someone on this forum? Like AliGW footnote is describing, i added reputation to your accounts.

As for sandy, if you happen to be able to do this in PQ then i am very interested in reading into it. Though, i can imagine you want to help someone else in need whilst my problem seems to be solved.

[leelnich]

Try this in B2, copied down:

Please Login or Register  to view this content.

[windknife]

You are welcome.
--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED

[fvdw]

One more question regarding your formula, is it correct that the formula sticks to row 1 untill 32 and does not progress on while dragging the forumla downward? It works well though.

If yes, can you explain what this part does, i am confused.

[sandy666]

As for sandy, if you happen to be able to do this in PQ then i am very interested in reading into it. Though, i can imagine you want to help someone else in need whilst my problem seems to be solved.

I'll try in my free time, but you are right - problem is solved

[HansDouwe]

Another option: Please try in B2 and copy down:

Please Login or Register  to view this content.

[windknife]

$1:$32 assume the max length of bom. You can adjust the number to fit the max length of bom.

If you want to drag a larger range, you can modify the formula as follows and attachement.

C2

Please Login or Register  to view this content.