Creating a unique number from a list of items

bristolrob

New Member
Joined
Apr 23, 2018
Messages
34
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
See if this is any use.

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

Excel Workbook
AB
1IndentUnique number
20001
31001001
41001002
52001002001
62001002002
71001003
82001003001
93001003001001
104001003001001001
114001003001001002
121001004
Indents
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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.

[TABLE="width: 331"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]INDENT[/TD]
[TD][/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]001001003001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001001003001003001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001004[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001005[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001006[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001007[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001008[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001001003001003001008001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001008001001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001008001002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001001003001003001008001003[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
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
AB
1IndentUnique number
20001
31001001
42001001001
52001001002
62001001003
71001002
82001002001
92001002002
102001002003
111001003
122001003001
132001003002
142001003003
152001003004
162001003005
172001003006
182001003007
192001003008
201001004
212001004001
222001004002
232001004003
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.
 
Upvote 0
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.
 
Upvote 0
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

[TABLE="width: 351"]
<colgroup><col><col></colgroup><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]3[/TD]
[TD]001004007003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004007003001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004007003002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004007003003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004007003004[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004007003005[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001004007003006[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001004007003006001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001004007003006002[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top