Creating a unique number from a list of items

[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 reverting back to level 1 again, so the indents will go 1,2,3,4,5, then back to 1,2,3 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

Indent Unique number
0 001
1 001001
1 001002
2 001002001
2 001002002
1 001003
2 001003001
3 001003001001
4 001003001001001
4 001003001001002
1 001004

 

[Peter_SSs]

See if this is any use.

B2 is formatted as Text and contains the value "001"
B3 formula is copied down.

Excel Workbook
	A	B
1	Indent	Unique number
2	0	001
3	1	001001
4	1	001002
5	2	001002001
6	2	001002002
7	1	001003
8	2	001003001
9	3	001003001001
10	4	001003001001001
11	4	001003001001002
12	1	001004
Indents

 

[Peter_SSs]

With so many rows of data, my formula suggestion may slow your sheet too much. If that is the case, or you were looking for a macro approach anyway, you could try this macro. It is based on the same layout as above, again with cell B2 as described in my previous post.

Sub Indent_Numbers()
  Dim a As Variant, b As Variant
  Dim BaseNum As String
  Dim i As Long, CountOnes As Long
  
  BaseNum = Range("B2").Value
  a = Range("A3", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1) As String
  For i = 1 To UBound(a)
    Select Case a(i, 1)
      Case 1
        CountOnes = CountOnes + 1
        b(i, 1) = BaseNum & Format(CountOnes, "000")
      Case a(i - 1, 1)
        b(i, 1) = Left(b(i - 1, 1), Len(b(i - 1, 1)) - 3) & Format(Right(b(i - 1, 1), 3) + 1, "000")
      Case Else
        b(i, 1) = b(i - 1, 1) & "001"
    End Select
  Next i
  Range("B3").Resize(UBound(b)).Value = b
End Sub

 

[bristolrob]

Many thanks for your solutions. I am however having an issue, when i try it in a blank workbook, it works until it needs to go back to level 1 where it jumps forwards rather than back.

INDENT
0	001
1	001001
2	001001001
2	001001002
2	001001003
1	001001003001
2	001001003001001
2	001001003001002
2	001001003001003
1	001001003001003001
2	001001003001003001001
2	001001003001003001002
2	001001003001003001003
2	001001003001003001004
2	001001003001003001005
2	001001003001003001006
2	001001003001003001007
2	001001003001003001008
1	001001003001003001008001
2	001001003001003001008001001
2	001001003001003001008001002
2	001001003001003001008001003

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

I am sure i am missing something rather than a problem with the formula
n.b i did format B2 as text and the remainder as general

 

[Peter_SSs]

Many thanks for your solutions. I am however having an issue, when i try it in a blank workbook, it works until it needs to go back to level 1 where it jumps forwards rather than back.

Is it doing that with both formula and macro? If not, which one is acting like that?

Here is my formula version with the data you just posted. Different results to you.

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
Indents 2 (formula)

If using the formula version and your layout is the same as mine, what does this formula, placed in an empty cell formatted as "General", return?
=LEN(A7)
Note that A7 is the cell with the second "1" value in column A.

Also, what does this return?
=ISNUMBER(A7)

If still unresolved, we might learn more from some small actual data posted (like mine) using one of the methods suggested in the link in my signature block below.

 

[bristolrob]

I did as you asked with the formula NOT the macro, and got the results =LEN(A7) answer is 1 and =ISNUMBER(A7) answer is FALSE

I have then highlighted the INDENT column and reformatted to number format with no decimal spaces, this has not made any difference to the results though.

 

[bristolrob]

I have now tried the macro as well and it solves the problem of going back to level 1.
However, a bit further down the item list it adds a level when going from 3 to 2 rather than taking a level away.
And again the responses to the 2 A7 questions are 1 and FALSE

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
3	001004007003
2	001004007003001
2	001004007003002
2	001004007003003
2	001004007003004
2	001004007003005
2	001004007003006
3	001004007003006001
3	001004007003006002

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

 

[West Man]

I have then highlighted the INDENT column and reformatted to number format with no decimal spaces, this has not made any difference to the results though.

Reformatting will not change text to numbers. In an empty cell formatted as number type the number 1. Copy that cell, select your indent data and paste special-multiply. The formula may then work.

 

[Peter_SSs]

However, a bit further down the item list it adds a level when going from 3 to 2 rather than taking a level away.

If your requirement includes gradually taking levels away, then neither my formula nor macro will work for you because in post #1 you clearly stated (& gave examples) that would not happen:

.. the indents will always go up in sequence before reverting back to level 1 again, so the indents will go 1,2,3,4,5, then back to 1,2,3 etc and then back to 1 again, and so on and so on down the item list.

 

[bristolrob]

Ok many many thanks for your efforts on this. I will post a new query and make sure i use the correct language.

I apologise for any confusion with my use of language