Create a unique number list

[bristolrob]

Ok so here is what I am after if anyone can help
I need to create a unique number from an item list, I am given indent numbers on the list for parent/child relationships
top level is level zero which is 001, next level with an indent of 1 will be 001001, next level with an indent of 2 will be 001001001, there may be a second indent of 2 under that giving you 001001002, and again there could be a 3rd,4th,5th indent of 2 underneath that giving you 001001005, this may go to up to 10 indents, the indents will always go up in sequence before possibly going back down in sequence Or back to level 1 again, so the indents might go 1,2,3,4,5,4,3 then jump back to 1,2,3,2,3,3,2 etc and then back to 1 again, and so on and so on down the item list.
This may be upwards of 20k items for an engine or vehicle etc.
The only way i have of doing this at the moment is manually which is extremely time consuming.
Any advice or help will be gratefully received.
Rob
Example below
A previous formula offered to solve this went up the list in correct sequence, but did not address going back down the list
(MY fault not the fault of the solution offered, many thanks to Peter_SSs)

[TABLE="class: cms_table, width: 351"]
<tbody>[TR]
[TD]INDENT[/TD]
[TD]Unique number[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002003[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003004[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003005[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003006[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003007[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001003008[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001004[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004004[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004005[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004006[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004007[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001004007001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001004007002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001004007002001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001004007003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004008[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001005[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001005001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001005001001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001005002[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001005002001[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001006[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

.. it is also not returning the leading 2 zero's

The formula still returns leading zeros for me. The only way I have been able to reproduce what you are showing in this regard is to copy the results and paste as values and then use the error-checking option to "convert numbers stored as text to numbers"

.. it is now for some reason not going back to the required level when going down i.e from 4 to 2 ...

The formula never did account for that. As with your previous thread on the same topic, it appears that you have given specific specifications of what can happen, but then subsequently change those specifications.

In this case, you specifically stated that if the indents went down, they would either go down sequentially or straight down to 1, not any other amount like from 4 to 2.

.. the indents will always go up in sequence before possibly going back down in sequence Or back to level 1 again, so the indents might go 1,2,3,4,5,4,3 then jump back to 1,2,3,2,3,3,2 etc

 

[Peter_SSs]

Okay, given that the indents can ..
a) Increase incrementally, or
b) Decrease by any amount
.. try this version.

Excel Workbook
	A	B
1	INDENT	Unique number
2	0	001
3	1	001001
4	1	001002
5	2	001002001
6	3	001002001001
7	3	001002001002
8	3	001002001003
9	3	001002001004
10	3	001002001005
11	2	001002002
12	3	001002002001
13	4	001002002001001
14	4	001002002001002
15	4	001002002001003
16	4	001002002001004
17	2	001002003
18	2	001002004
19	3	001002004001
20	4	001002004001001
21	4	001002004001002
22	4	001002004001003
23	4	001002004001004
24	4	001002004001005
25	4	001002004001006
26	4	001002004001007
27	4	001002004001008
28	4	001002004001009
29	1	001003
30	1	001004
31	1	001005
32	1	001006
Unique Num (4)

 

[Peter_SSs]

Actually, this much simpler one seems to also do the job.

Excel Workbook
	A	B
1	INDENT	Unique Number
2	0	001
3	1	001001
4	1	001002
5	2	001002001
6	3	001002001001
7	3	001002001002
8	3	001002001003
9	3	001002001004
10	3	001002001005
11	2	001002002
12	3	001002002001
13	4	001002002001001
14	4	001002002001002
15	4	001002002001003
16	4	001002002001004
17	2	001002003
18	2	001002004
19	3	001002004001
20	4	001002004001001
21	4	001002004001002
22	4	001002004001003
23	4	001002004001004
24	4	001002004001005
25	4	001002004001006
26	4	001002004001007
27	4	001002004001008
28	4	001002004001009
29	1	001003
30	1	001004
31	1	001005
Unique Num (5)