Complicated Automatic numbering

[Amanda Memery]

Hi, I'm new to this forum so please be gentle!

I have a logistics breakdown structure of hundreds of parts of a Bill of Materials all broken down into a Product Tree Form which I need to upload.

For Example:
Level A (main system) 1
Level B (sub system 1) 1.1, (subsystem 2) 1.2 (subsystem 3) 1.3
Level C (Part 1) 1.1.1, 1.1.2, 1.1.3 (LRU 2) 1.2.1, 1.2.2, 1.2.3 (LRU 3) 1.3.1, 1.3.2, 1.3.3
.....and so on to about Level I (about 6,000 rows in total to number all the way up to 9 Sub Systems (B))

I have several of these to load into some software via excel and I'm currently having to filter on the level and manually input the numbering (1.2.1 etc.). I could really do with a way of automating this but have no idea how to do this. I've tried a couple of approaches from forum threads but, as yet, none appear to be able to automate to the level I need.

Any help on how to do this would be fantastic as it's currently taking me about a week to do each system so automating this for future use is going to save me months.

Many Thanks in advance for your help

 

[Micron]

To start with I'd suggest you mock up some data in a sheet that illustrates what you're starting with and another block that shows what you need to end up with. Then paste those in a post. I've read that 3x and can't figure out what's what. I think I get the numbering sequence but have no idea if you're trying to copy it verbatim, duplicate a pattern or create on the fly because the number of rows and sub elements probably differs each time.

 

[Dave Patton]

search Internet for

Project-Style (WBS) Numbering in MS Excel

This may be what you require.

 

[Amanda Memery]

To start with I'd suggest you mock up some data in a sheet that illustrates what you're starting with and another block that shows what you need to end up with. Then paste those in a post. I've read that 3x and can't figure out what's what. I think I get the numbering sequence but have no idea if you're trying to copy it verbatim, duplicate a pattern or create on the fly because the number of rows and sub elements probably differs each time.

OK so here is a very simple mock up of the type of thing I'm talking about. I basically need to end up with an automatic indented number system linked to the correct levels and subsystem. In terms of the data, it will be slightly different for every different Tree structure I do. For example, going forward every time a new product is launched with a part structure it will all need to be identified in a new spreadsheet and a new number sequence. So in answer to your last question, going forward the number of rows and sub elements will differ each time.

I hope that helps create a better picture of what I'm after.

 

[Micron]

From that I can only imagine the point at which you increment from 1.1 to 1.2 is either the word "subsystem" or the fact that structure is 2 characters long. If that is the case, it shouldn't be too hard to inject those numbers. If the rest of it is not based on character length then I have no idea what dictates whether to incrementally append or start a new level. It seems to me you'd build an array (2,3,5,7), set i = 1 then loop through the data from top to bottom.

The logic would be that if Len(Structure) = array(i) [1st time thru i=2) then Tree = 1, then next match = 2, next = 3 ... until the end. Then increment: i = i+1
Loop again and If Len(Structure) = array(i) [i now =3] then concatenate .1 to the preceding value (not the preceding entry) and insert that below; next concatenate is .2, next is .3 until end
Repeat for values of Len 5 and again for 7. A lot of coding work IMO but if I've got the logic figured out, it should be doable.

Certainly do give post 3 some consideration.

 

[Rick Rothstein]

A couple of questions....

1) Will there be a second (and third, etc.) "Main System" type entry (Level A) that would be numbered 2 (and 3, etc.)?

2) What cell is the word "Item" In?

3) The column labeled "Level" is part of your actual data, correct?

 

[Dave Patton]

Did you review the information on WBS numbering -post #3?


Project-Style (WBS) Numbering in MS Excel
In a project Work Breakdown Structure (WBS), tasks are organized into major tasks, sub-tasks, sub-sub-tasks, etc. as in the following example which is an actual project plan used by NASA:

The VBA provides the numbering based on the indent level of the data.

T10_1701a.xlsm
	A	B
1		Task
2	1	Main system
3	1.1	Sub system 1
4	1.1.1	Console
5	1.1.2	Keyboard
6	1.1.3	Hard drive
7	1.1.3.1	Memory
8	1.1.3.2	Wire
9	1.2	Sub system 2
10
4cc

 

[Amanda Memery]

From that I can only imagine the point at which you increment from 1.1 to 1.2 is either the word "subsystem" or the fact that structure is 2 characters long. If that is the case, it shouldn't be too hard to inject those numbers. If the rest of it is not based on character length then I have no idea what dictates whether to incrementally append or start a new level. It seems to me you'd build an array (2,3,5,7), set i = 1 then loop through the data from top to bottom.

The logic would be that if Len(Structure) = array(i) [1st time thru i=2) then Tree = 1, then next match = 2, next = 3 ... until the end. Then increment: i = i+1
Loop again and If Len(Structure) = array(i) [i now =3] then concatenate .1 to the preceding value (not the preceding entry) and insert that below; next concatenate is .2, next is .3 until end
Repeat for values of Len 5 and again for 7. A lot of coding work IMO but if I've got the logic figured out, it should be doable.

Certainly do give post 3 some consideration.

Yes that's correct. The point at which I increment from 1.1 to 1.2 is the level "B" in this instance. Then any "c" levels need to add to the increment above so under 1.1 "B" Should be 1.1.1 "C" and so on so the 1.2 "B" level will then be 1.2.1 at level "C". Not the most straightforward but the level is generated by the structure character length. i.e. anything that's 3 characters is a "B" Level anything that's 5 is a "C" Level and so on.

I did have a look at WBS in excel tutorials but wasn't sure it would work for this, but perhaps I need to review this. But yes, your logic sounds like what I'm after. I'm not a huge coder so was looking for the correct formulas to enter. If I have a go at the above formula will that work for the whole list or are there other formula that I need to add as I go down through the list. I'm not too bothered about a lot of coding as long as I can re-use the formula to work out other future systems. Do the job well once and all that...

Thanks for you help on this..

 

[Micron]

Excel formulae are not my thing - mainly it's Access vba and Excel vba to a lesser extent so I can't answer your question. I have not looked at WBS but @Dave Patton seems to think it is what you need.

 

[fjns]

Hi, see the linked file (with two auxiliary columns) for a possible solution...

The formulas used in the table:
D3: =IF(CODE(C3)=CODE(C2),LEFT(D2,F2)&TEXT(VALUE(RIGHT(D2,LEN(D2)-F2))+1,"0"),IF(CODE(C3)=CODE(C2)+1,D2&".1",IF(AND(CODE(C3)>=CODE(C$2),CODE(C3)<CODE(C2)),LEFT(INDEX(D$2:D2,MATCH(LEFT(E2,SEARCH(CHAR(CODE(C3)+1),E2)-2),E$2:E2,0)),INDEX(F$2:F2,MATCH(LEFT(E2,SEARCH(CHAR(CODE(C3)+1),E2)-2),E$2:E2,0)))&TEXT(VALUE(MID(E2,SEARCH(C3,E2)+1,SEARCH(CHAR(CODE(C3)+1),E2)-SEARCH(C3,E2)-2))+1,"0"),""))) (Range: D3:D100)
E3: =IF(CODE(C3)=CODE(C2),LEFT(E2,LEN(E2)-LEN(D2)+F2)&TEXT(VALUE(RIGHT(D2,LEN(D2)-F2))+1,"0"),IF(CODE(C3)=CODE(C2)+1,E2&"."&C3&"1",IF(AND(CODE(C3)>=CODE(C$2),CODE(C3)<CODE(C2)),LEFT(E2,SEARCH(C3,E2))&TEXT(VALUE(MID(E2,SEARCH(C3,E2)+1,SEARCH(CHAR(CODE(C3)+1),E2)-SEARCH(C3,E2)-2))+1,"0"),""))) (Range: E3:E100)
F3: =IF(CODE(C3)=CODE(C2),F2,IF(CODE(C3)=CODE(C2)+1,LEN(D2)+1,IF(AND(CODE(C3)>=CODE(C$2),CODE(C3)<CODE(C2)),INDEX(F$2:F2,MATCH(LEFT(E2,SEARCH(CHAR(CODE(C3)+1),E2)-2),E$2:E2,0)),0))) (Range: F3:F100)

(Sorry, my English is too weak.) (I practice English and Excel on this forum.)

ProductTree.xlsx