Create a unique number list

B

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 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]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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
ABC
1INDENTUnique numberUnique number Test
2011
3110011001
4210010011001001
5210010021001002
6210010031001003
7110021002
8210020011002001
9210020021002002
10210020031002003
11110031003
12210030011003001
13210030021003002
14210030031003003
15210030041003004
16210030051003005
17210030061003006
18210030071003007
19210030081003008
20110041004
21210040011004001
22210040021004002
23210040031004003
24210040041004004
25210040051004005
26210040061004006
27210040071004007
28310040070011004007001
29310040070021004007002
30410040070020011004007002001
31310040070031004007003
32210040081004008
33110051005
34210050011005001
35310050010011005001001
36210050021005002
37310050020011005002001
38110061006
Sheet1
 
Last edited:
Upvote 0
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

[TABLE="width: 477"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1005036008[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1005036009[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1.00504E+12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1.00504E+12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1.00504E+12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1.00504E+12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]1.00504E+15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]1.00504E+15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]1.00504E+15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1.00504E+12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1005037[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1005038[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1005038001
[/TD]
[/TR]
</tbody>[/TABLE]


Is this likely to be a formatting issue ?
 
Upvote 0
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)
 
Upvote 0
bristolrob said:
Any way to get the 00 at the front of level zero ?
Click to expand...
This adaptation of VBA Geek's suggestion would cope with that issue ...

Leave B1 blank.
B2, copied down.

Excel Workbook
AB
1INDENT
20001
31001001
42001001001
52001001002
62001001003
71001002
82001002001
92001002002
102001002003
111001003
122001003001
132001003002
142001003003
152001003004
162001003005
172001003006
182001003007
192001003008
201001004
212001004001
222001004002
232001004003
242001004004
252001004005
262001004006
272001004007
283001004007001
293001004007002
304001004007002001
313001004007003
322001004008
331001005
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
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
242001004004
252001004005
262001004006
272001004007
283001004007001
293001004007002
304001004007002001
313001004007003
322001004008
331001005
342001005001
353001005001001
362001005002
373001005002001
381001006
392001006001
403001006001001
414001006001001001
425001006001001001001
436001006001001001001001
446001006001001001001002
457001006001001001001002001
468001006001001001001002001001
479001006001001001001002001001001
4810001006001001001001002001001001001
499001006001001001001002001001002
509001006001001001001002001001003
518001006001001001001002001002
Unique Num (2)
 
Upvote 0
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 !!!
 
Upvote 0
bristolrob said:
Good evening Peter
You have cracked it sir !!!
It works all the way down my lists.
Click to expand...
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.
Code: 
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



bristolrob said:
Any way i can thank you ?
Please let me know
Warmest regards and Bravo !!!
Click to expand...
You just did. :biggrin:
 
Upvote 0
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
 
Upvote 0
bristolrob said:
Could you just confirm the correct cell formatting for column A and column B
Click to expand...
Mine are formatted as General


bristolrob said:
For some reason my formula is no longer returning the results i am after
Click to expand...
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?
 
Upvote 0
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.


[TABLE="width: 349"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]INDENT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1001[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1002001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1002001001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1002001002[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1002001003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1002001004[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1002001005[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1002002[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1002002001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002001001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002001002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002001003[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002001004[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1002002002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1002002003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1002002003001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001003[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001004[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001005[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001006[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001007[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001008[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002002003001009[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1002002003002[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1002002003003[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1002002003004[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1002002003005[/TD]
[/TR]
</tbody>[/TABLE]


What it should be returning is below

INDENT
[TABLE="width: 491"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]0[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001001[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001002[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001004[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001005[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002002[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002002001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001003[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001004[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002004[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002004001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001003[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001004[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001005[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001006[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001007[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001008[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001009[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001003[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001004[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001005[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001006[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

SanjayGMusafir
  • Solved
Filtering a list further
Replies
3
Views
375
Fluff
Fluff
S
  • Question Question
Excel Customer List to Word Offer and Invoice template, with unique invoice number list and buttons to create
Replies
0
Views
515
_SGX_
S
B
  • Question Question
Creating a unique number from a list of items
Replies
9
Views
870
bristolrob
B
B
  • Locked
  • Question Question
Unique Number Formula Required
Replies
5
Views
730
Peter_SSs
Peter_SSs
C
  • Question Question
Excel 2010 VBA: Generating a unique batch reference using letters and numbers
Replies
2
Views
979
coolbear90
C

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

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