Unique Number Formula Required

[bristolrob]

Good morning all
Here is what I am after,
I need a formula to create a unique number for each item on a list of engine parts
Below is what i currently undertake manually and consists of typing in each level and then concatenating the levels at the end of the process.
This can be in excess of 10k lines per platform, so you can see it takes a substantial amount of effort.
If i can get a formula to create the Unique Number from the INDENT column then i can break out the levels quite easily .
The INDENT column always has to start with a 0 (only once) to indicate the overarching platform, the INDENT numbers can go up to 10 on larger platforms and can ONLY go UP in sequence 1,2,3,4 etc BUT they can drop back to any point below i.e 4 to 1 or 8 to 3 etc.
I currently format INDENT to number, Unique number to general and the Lvls to text, so please let me know if your formula changes any of these.
I really appreciate the help and advice this board offers and I hope someone can help

[TABLE="width: 1079"]
<colgroup><col><col><col span="9"></colgroup><tbody>[TR]
[TD]INDENT[/TD]
[TD]UNIQUE NUMBER[/TD]
[TD]LVL1[/TD]
[TD]LVL2[/TD]
[TD]LVL3[/TD]
[TD]LVL4[/TD]
[TD]LVL5[/TD]
[TD]LVL6[/TD]
[TD]LVL7[/TD]
[TD]LVL8[/TD]
[TD]LVL9[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]001[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001001[/TD]
[TD]001[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001002[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001002[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001003[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001004[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002001005[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002002[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002002001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001002[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001003[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002002001004[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD]001[/TD]
[TD]004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002003[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001002004[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001002004001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001002[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001003[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001004[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001005[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001006[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001007[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001008[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001002004001009[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]004[/TD]
[TD]001[/TD]
[TD]009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001003[/TD]
[TD]001[/TD]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001004[/TD]
[TD]001[/TD]
[TD]004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001005[/TD]
[TD]001[/TD]
[TD]005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001006[/TD]
[TD]001[/TD]
[TD]006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001007[/TD]
[TD]001[/TD]
[TD]007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001008[/TD]
[TD]001[/TD]
[TD]008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001009[/TD]
[TD]001[/TD]
[TD]009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001010[/TD]
[TD]001[/TD]
[TD]010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001011[/TD]
[TD]001[/TD]
[TD]011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001012[/TD]
[TD]001[/TD]
[TD]012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001013[/TD]
[TD]001[/TD]
[TD]013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001014[/TD]
[TD]001[/TD]
[TD]014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001014001[/TD]
[TD]001[/TD]
[TD]014[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001014001001[/TD]
[TD]001[/TD]
[TD]014[/TD]
[TD]001[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001015[/TD]
[TD]001[/TD]
[TD]015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]001015001[/TD]
[TD]001[/TD]
[TD]015[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001015001001[/TD]
[TD]001[/TD]
[TD]015[/TD]
[TD]001[/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001015001002[/TD]
[TD]001[/TD]
[TD]015[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]001015001003[/TD]
[TD]001[/TD]
[TD]015[/TD]
[TD]001[/TD]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001016[/TD]
[TD]001[/TD]
[TD]016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001017[/TD]
[TD]001[/TD]
[TD]017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]001018[/TD]
[TD]001[/TD]
[TD]018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

If I understand correctly.

Only Column B needs to be formatted as Text, I have Columns A, C onward formatted General.

Book1
	A	B	C	D	E	F	G
1	INDENT	UNIQUE NUMBER	LVL1	LVL2	LVL3	LVL4	LVL5
2	0	001	001
3	1	001001	001	001
4	1	001002	001	002
5	2	001002001	001	002	001
6	3	001002001001	001	002	001	001
7	3	001002001002	001	002	001	002
8	3	001002001003	001	002	001	003
9	3	001002001004	001	002	001	004
10	3	001002001005	001	002	001	005
11	2	001002002	001	002	002
12	3	001002002001	001	002	002	001
13	4	001002002001001	001	002	002	001	001
14	4	001002002001002	001	002	002	001	002
15	4	001002002001003	001	002	002	001	003
16	4	001002002001004	001	002	002	001	004
17	2	001002003	001	002	003
18	2	001002004	001	002	004
19	3	001002004001	001	002	004	001
20	4	001002004001001	001	002	004	001	001
21	4	001002004001002	001	002	004	001	002
22	4	001002004001003	001	002	004	001	003
23	4	001002004001004	001	002	004	001	004
24	4	001002004001005	001	002	004	001	005
25	4	001002004001006	001	002	004	001	006
26	4	001002004001007	001	002	004	001	007
27	4	001002004001008	001	002	004	001	008
28	4	001002004001009	001	002	004	001	009
29	1	001003	001	003
30	1	001004	001	004
31	1	001005	001	005
32	1	001006	001	006
33	1	001007	001	007
34	1	001008	001	008
35	1	001009	001	009
36	1	001010	001	010
37	1	001011	001	011
38	1	001012	001	012
39	1	001013	001	013
40	1	001014	001	014
41	2	001014001	001	014	001
42	3	001014001001	001	014	001	001
43	1	001015	001	015
44	2	001015001	001	015	001
45	3	001015001001	001	015	001	001
46	3	001015001002	001	015	001	002
47	3	001015001003	001	015	001	003
48	1	001016	001	016
49	1	001017	001	017
50	1	001018	001	018
Sheet108

Cell Formulas
Range	Formula
A2	=LEN(B2)/3-1
C2	=MID($B2,COLUMNS($C2:C2)*3-2,3)

A2 formula copied Down.
C2 formula copied Down And Across as far as needed.

 

[bristolrob]

Hi there and thanks for the reply... It is the B2 column formula that I am after using the indent column to create the unique number

 

[Joyner]

Hi there and thanks for the reply... It is the B2 column formula that I am after using the indent column to create the unique number

What exactly do you need? From what you show, column B is just a concatenation of the other columns, LVL1&LVL2...etc

 

[bristolrob]

Hi there and yes currently I concatenate from the lvls which I input manually from the indent column.... If I could get the unique number using the indent column then I can easily extract the levels... So extract B from A then C from B... I hope that makes it a bit clearer what it is I am after and thanks again for helping out

 

[Peter_SSs]

Duplicate to post #10 here

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.

Also refer to my suggested formula in post 13 of that thread.