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)

INDENT	Unique number
0	001
1	001001
2	001001001
2	001001002
2	001001003
1	001002
2	001002001
2	001002002
2	001002003
1	001003
2	001003001
2	001003002
2	001003003
2	001003004
2	001003005
2	001003006
2	001003007
2	001003008
1	001004
2	001004001
2	001004002
2	001004003
2	001004004
2	001004005
2	001004006
2	001004007
3	001004007001
3	001004007002
4	001004007002001
3	001004007003
2	001004008
1	001005
2	001005001
3	001005001001
2	001005002
3	001005002001
1	001006

<tbody>
</tbody>

 

[VBA Geek]

Try:


<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=INT(<font color="Blue">C2*1000^(<font color="Red">A3-A2</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013/2016
	A	B	C
1	INDENT	Unique number	Unique number Test
2	0	1	1
3	1	1001	1001
4	2	1001001	1001001
5	2	1001002	1001002
6	2	1001003	1001003
7	1	1002	1002
8	2	1002001	1002001
9	2	1002002	1002002
10	2	1002003	1002003
11	1	1003	1003
12	2	1003001	1003001
13	2	1003002	1003002
14	2	1003003	1003003
15	2	1003004	1003004
16	2	1003005	1003005
17	2	1003006	1003006
18	2	1003007	1003007
19	2	1003008	1003008
20	1	1004	1004
21	2	1004001	1004001
22	2	1004002	1004002
23	2	1004003	1004003
24	2	1004004	1004004
25	2	1004005	1004005
26	2	1004006	1004006
27	2	1004007	1004007
28	3	1004007001	1004007001
29	3	1004007002	1004007002
30	4	1004007002001	1004007002001
31	3	1004007003	1004007003
32	2	1004008	1004008
33	1	1005	1005
34	2	1005001	1005001
35	3	1005001001	1005001001
36	2	1005002	1005002
37	3	1005002001	1005002001
38	1	1006	1006
Sheet1

 

[bristolrob]

Many thanks for the prompt reply
Any way to get the 00 at the front of level zero ?
Also when i get down to level 4,s and 5s i get the following results

3		1005036008
3		1005036009
4		1.00504E+12
4		1.00504E+12
4		1.00504E+12
4		1.00504E+12
5		1.00504E+15
5		1.00504E+15
5		1.00504E+15
4		1.00504E+12
2		1005037
2		1005038
3		1005038001

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Is this likely to be a formatting issue ?

 

[VBA Geek]

it is a formatting issue, you need to select the column and format it as NUMBER (no decimals).

for the first value you can type '001 or format the cell as 000 (only the first cell)

 

[Peter_SSs]

Any way to get the 00 at the front of level zero ?

This adaptation of VBA Geek's suggestion would cope with that issue ...

Leave B1 blank.
B2, copied down.

Excel Workbook
	A	B
1	INDENT
2	0	001
3	1	001001
4	2	001001001
5	2	001001002
6	2	001001003
7	1	001002
8	2	001002001
9	2	001002002
10	2	001002003
11	1	001003
12	2	001003001
13	2	001003002
14	2	001003003
15	2	001003004
16	2	001003005
17	2	001003006
18	2	001003007
19	2	001003008
20	1	001004
21	2	001004001
22	2	001004002
23	2	001004003
24	2	001004004
25	2	001004005
26	2	001004006
27	2	001004007
28	3	001004007001
29	3	001004007002
30	4	001004007002001
31	3	001004007003
32	2	001004008
33	1	001005
Unique Num

... but you would still have the same issue with larger number indents due to Excel's limits of significant digits.


I think this formula, copied down, does what you want.

Excel Workbook
	A	B
1	INDENT	Unique number
2	0	001
3	1	001001
4	2	001001001
5	2	001001002
6	2	001001003
7	1	001002
8	2	001002001
9	2	001002002
10	2	001002003
11	1	001003
12	2	001003001
13	2	001003002
14	2	001003003
15	2	001003004
16	2	001003005
17	2	001003006
18	2	001003007
19	2	001003008
20	1	001004
21	2	001004001
22	2	001004002
23	2	001004003
24	2	001004004
25	2	001004005
26	2	001004006
27	2	001004007
28	3	001004007001
29	3	001004007002
30	4	001004007002001
31	3	001004007003
32	2	001004008
33	1	001005
34	2	001005001
35	3	001005001001
36	2	001005002
37	3	001005002001
38	1	001006
39	2	001006001
40	3	001006001001
41	4	001006001001001
42	5	001006001001001001
43	6	001006001001001001001
44	6	001006001001001001002
45	7	001006001001001001002001
46	8	001006001001001001002001001
47	9	001006001001001001002001001001
48	10	001006001001001001002001001001001
49	9	001006001001001001002001001002
50	9	001006001001001001002001001003
51	8	001006001001001001002001002
Unique Num (2)

 

[bristolrob]

Good evening Peter
You have cracked it sir !!!
It works all the way down my lists.
This has saved me a serious amount of time.
Any way i can thank you ?
Please let me know
Warmest regards and Bravo !!!

 

[Peter_SSs]

Good evening Peter
You have cracked it sir !!!
It works all the way down my lists.

Good news!
I was wondering though if the sheet becomes a bit sluggish with such a formula copied down 20,000 rows?
If it is, you could also consider this macro that should produce the same results very quickly. If the 'Indent' numbers can change, we might have to do a bit more as, at the moment, the results of the macro will not change if an indent number changes. Anyway, you could give it a try in a copy of your workbook.

Sub Indent_Numbers()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim s As String
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1) As String
  b(1, 1) = "001"
  For i = 2 To UBound(a)
    If a(i, 1) = 1 Then
      k = k + 1
      b(i, 1) = b(1, 1) & Format(k, "000")
    Else
      Select Case Sgn(a(i, 1) - a(i - 1, 1))
        Case -1
          b(i, 1) = Left(s, Len(s) - 6) & Format(Left(Right(s, 6), 3) + 1, "000")
        Case 0
          b(i, 1) = Left(s, Len(s) - 3) & Format(Right(s, 3) + 1, "000")
        Case 1
          b(i, 1) = s & "001"
      End Select
    End If
    s = b(i, 1)
  Next i
  Range("B2").Resize(UBound(b)).Value = b
End Sub

Any way i can thank you ?
Please let me know
Warmest regards and Bravo !!!

You just did.

 

[bristolrob]

Good morning Peter

For some reason my formula is no longer returning the results i am after

Could you just confirm the correct cell formatting for column A and column B

Many many thanks as ever

Robert

 

[Peter_SSs]

Could you just confirm the correct cell formatting for column A and column B

Mine are formatted as General

For some reason my formula is no longer returning the results i am after

That doesn't give me much to go on.

- Please identify which formula you are referring to.

- Could we have some small sample data & explain what results the formula is now returning together with what it should be returning?

 

[bristolrob]

Using the formula below with both column A and B set to general cell format,

=IF(A2=0,"001",IF(A2=1,B$2&TEXT(COUNTIF(A$2:A2,1),"000"),CHOOSE(SIGN(A2-A1)+2,LEFT(B1,LEN(B1)-6)&TEXT(LEFT(RIGHT(B1,6),3)+1,"000"),LEFT(B1,LEN(B1)-3)&TEXT(RIGHT(B1,3)+1,"000"),B1&"001")))

It is now returning the below results, as you can see it is now for some reason not going back to the required level when going down i.e from 4 to 2 or from 4 to 1 which it did do before (very odd) it is also not returning the leading 2 zero's
I admit i have not used it for a few weeks which is why i asked the question about formatting the cells.

INDENT
0	1
1	1001
1	1002
2	1002001
3	1002001001
3	1002001002
3	1002001003
3	1002001004
3	1002001005
2	1002002
3	1002002001
4	1002002001001
4	1002002001002
4	1002002001003
4	1002002001004
2	1002002002
2	1002002003
3	1002002003001
4	1002002003001001
4	1002002003001002
4	1002002003001003
4	1002002003001004
4	1002002003001005
4	1002002003001006
4	1002002003001007
4	1002002003001008
4	1002002003001009
1	1002002003002
1	1002002003003
1	1002002003004
1	1002002003005

<colgroup><col><col></colgroup><tbody>
</tbody>

What it should be returning is below

INDENT

0	001
1	001001
1	001002
2	001002001
3	001002001001
3	001002001002
3	001002001003
3	001002001004
3	001002001005
2	001002002
3	001002002001
4	001002002001001
4	001002002001002
4	001002002001003
4	001002002001004
2	001002003
2	001002004
3	001002004001
4	001002004001001
4	001002004001002
4	001002004001003
4	001002004001004
4	001002004001005
4	001002004001006
4	001002004001007
4	001002004001008
4	001002004001009
1	001003
1	001004
1	001005
1	001006

<colgroup><col><col></colgroup><tbody>
</tbody>