I need the formula for WBS No. of my tasks

[suri7891]

Hi there,

I have all my main task and subtasks in the same row. I need the formula to generate the WBS No. of each task according to the main task, sub task and also the sub task's of sub task.

Please find the sample below. All main task, sub tasks and sub task's of sub tasks are separated by "|" symbol. Please help with the formula to generated the Task Outline number.

Task Name	TaskOutlineNumber
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	0
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services	1
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Client Training	1.1
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Dev Ops	1.2
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Environment Management	1.3
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Non-Chargeable Activities	1.4
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Non-Chargeable Activities|Core Bug Fixing	1.4.1
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Non-Chargeable Activities|Environment Management (non-chargeable)	1.4.2
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Non-Chargeable Activities|Internal Training	1.4.3
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Non-Chargeable Activities|System Testing	1.4.4
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Implementation Services|Non-Chargeable Activities|Travel	1.4.5
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Project Services	2
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Project Services|Conversion Delivery Management	2.1
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Project Services|PMO	2.2
8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)|Project Services|Project Management	2.3

 

[suri7891]

Hi Peter,

Your Power query is working fine for a single project. can you please provide the formula if the Task Name.1 changes also, level one should be 1?
I hope you understood my requirement. Please advise

Hi Peter,

Thanks for your effort. Your formulas and steps are helping me. But I have bunch of projects, tasks and sub tasks. So, it is taking a lot of time to apply for all the tasks in my bucket. Do you have any generalized formula to the cell? I mean - The formula should consider "|" as the subtasks and give us the outline numbers as 1.1.1, 1.1.2, 1.2.1, etc.

 

[suri7891]

Example of the projects

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Project Services

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Project Services|Project Management

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Project Services|PMO

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Project Services|Conversion Delivery Management

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Environment Management

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Dev Ops

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Client Training

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Non-Chargeable Activities

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Non-Chargeable Activities|Environment Management (non-chargeable)

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Non-Chargeable Activities|Internal Training

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Non-Chargeable Activities|Travel

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Non-Chargeable Activities|Core Bug Fixing

AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)|Implementation Services|Non-Chargeable Activities|System Testing

AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)|Build and Test

AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)|UAT Execution

AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)|Go-Live Migration

AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)|Out of Hours Activity

AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)|Non-Chargeable Activities

AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)|Non-Chargeable Activities|Defect Triaging

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Project Services

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Project Services|Project Management

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Project Services|PMO

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Project Services|Conversion Delivery Management

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Environment Management

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Dev Ops

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Client Training

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Non-Chargeable Activities

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Non-Chargeable Activities|Environment Management (non-chargeable)

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Non-Chargeable Activities|Internal Training

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Non-Chargeable Activities|Travel

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Non-Chargeable Activities|Core Bug Fixing

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)|Implementation Services|Non-Chargeable Activities|System Testing

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)|Build and Test

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)|UAT Execution

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)|Go-Live Migration

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)|Out of Hours Activity

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)|Non-Chargeable Activities

AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)|Non-Chargeable Activities|Defect Triaging

Hi Peter,

Your Power query is working fine for a single project. can you please provide the formula if the Task Name.1 changes also, level one should be 1?
I hope you understood my requirement. Please advise

 

[suri7891]

Hi Peter,

Please check the below screenshot. I got 2 projects here. I want the formula to consider my project name change in column 1.1 Then, I need to get the WBS no. accordingly. Do you know the formula? Please advise

Column1.1	Column1.2	Column1.3	Column1.8	Column1.9	Column1.10
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services		1​	1​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services	Project Management	1​	1​	1​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services	PMO	1​	1​	2​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services	Conversion Delivery Management	1​	1​	3​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services		1​	2​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Environment Management	1​	2​	1​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Dev Ops	1​	2​	2​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Client Training	1​	2​	3​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	1​	2​	4​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	1​	2​	4​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	1​	2​	4​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	1​	2​	4​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	1​	2​	4​
AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	1​	2​	4​
AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Build and Test		2​	3​
AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	UAT Execution		2​	4​
AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Go-Live Migration		2​	5​
AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Out of Hours Activity		2​	6​
AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Non-Chargeable Activities	​	2​	7
AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Non-Chargeable Activities	Defect Triaging	2​	7​	1​

Hi Peter,

Thanks for your effort. Your formulas and steps are helping me. But I have bunch of projects, tasks and sub tasks. So, it is taking a lot of time to apply for all the tasks in my bucket. Do you have any generalized formula to the cell? I mean - The formula should consider "|" as the subtasks and give us the outline numbers as 1.1.1, 1.1.2, 1.2.1, etc.

 

[Dave Patton]

" Hi Dave, Thanks for your effort. I am unable to take the formula which is in the table. Please advise."

I said in post #5 that there was a solution on the www. That solution generates the WBS numbers based on the indent position.
I posted a tiny example to show that it works. The post did not include a formula.

You can refer to that article or just ignore that alternative.

 

[pjmorris]

Hi,

I've reviewed the above posts and believe I understand the problem. The attached below shows amended formula's which should help. If not please highlight which WBS entries are incorrect and what value you would like them to have.

HTH

Book1
	A	B	C	D	E	F	G	H	I
1	Task Name.1	Task Name.2	Task Name.3	Task Name.4	Level 1	Level 2	Level 3	Level 4	WBS
2	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services			1	1			1.1
3	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services	Project Management		1	1	1		1.1.1
4	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services	PMO		1	1	2		1.1.2
5	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Project Services	Conversion Delivery Management		1	1	3		1.1.3
6	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services			1	2			1.2
7	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Environment Management		1	2	1		1.2.1
8	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Dev Ops		1	2	2		1.2.2
9	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Client Training		1	2	3		1.2.3
10	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities		1	2	4		1.2.4
11	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	Environment Management (non-chargeable)	1	2	4	1	1.2.4.1
12	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	Internal Training	1	2	4	2	1.2.4.2
13	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	Travel	1	2	4	3	1.2.4.3
14	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	Core Bug Fixing	1	2	4	4	1.2.4.4
15	AUCN - 4054364 HSBC Upgrade Reconciliations (Implementation)	Implementation Services	Non-Chargeable Activities	System Testing	1	2	4	5	1.2.4.5
16	AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Build and Test			2	1			2.1
17	AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	UAT Execution			2	2			2.2
18	AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Go-Live Migration			2	3			2.3
19	AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Out of Hours Activity			2	4			2.4
20	AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Non-Chargeable Activities			2	5			2.5
21	AUCN - 4054364 HSBC Upgrade Reconciliations (REC Upgrade)	Non-Chargeable Activities	Defect Triaging		2	5	1		2.5.1
22	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Project Services			3	1			3.1
23	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Project Services	Project Management		3	1	1		3.1.1
24	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Project Services	PMO		3	1	2		3.1.2
25	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Project Services	Conversion Delivery Management		3	1	3		3.1.3
26	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services			3	2			3.2
27	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Environment Management		3	2	1		3.2.1
28	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Dev Ops		3	2	2		3.2.2
29	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Client Training		3	2	3		3.2.3
30	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Non-Chargeable Activities		3	2	4		3.2.4
31	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Non-Chargeable Activities	Environment Management (non-chargeable)	3	2	4	1	3.2.4.1
32	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Non-Chargeable Activities	Internal Training	3	2	4	2	3.2.4.2
33	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Non-Chargeable Activities	Travel	3	2	4	3	3.2.4.3
34	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Non-Chargeable Activities	Core Bug Fixing	3	2	4	4	3.2.4.4
35	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (Implementation)	Implementation Services	Non-Chargeable Activities	System Testing	3	2	4	5	3.2.4.5
36	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)	Build and Test			4	1			4.1
37	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)	UAT Execution			4	2			4.2
38	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)	Go-Live Migration			4	3			4.3
39	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)	Out of Hours Activity			4	4			4.4
40	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)	Non-Chargeable Activities			4	5			4.5
41	AUCN - 4659661 ANZ DC Matching - WinSvr 2016 migration (REC Upgrade)	Non-Chargeable Activities	Defect Triaging		4	5	1		4.5.1
Sheet3

Cell Formulas
Range	Formula
E2:E41	E2	=IF([@[Task Name.1]]<>"",IF(A2<>A1,IFERROR(E1+1,1),E1),"")
F2:F41	F2	=IF([@[Task Name.2]]<>"",IF(B2<>B1,IFERROR(IF(E1=[@[Level 1]],F1+1,1),1),F1),"")
G2:G41	G2	=IF([@[Task Name.3]]<>"",IF(C2<>C1,IFERROR(IF(F1=[@[Level 2]],G1+1,1),1),G1),"")
H2:H41	H2	=IF([@[Task Name.4]]<>"",IF(D2<>D1,IFERROR(IF(G1=[@[Level 3]],H1+1,1),1),H1),"")
I2:I41	I2	=[@[Level 1]] & IF([@[Level 2]]<>"","."&[@[Level 2]] & IF([@[Level 3]]<>"","."&[@[Level 3]] & IF([@[Level 4]]<>"","."&[@[Level 4]],""),""),"")

Named Ranges
Name	Refers To	Cells
ExternalData_1	=Sheet3!$A$1:$D$41	E2