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

 

[arthurbr]

I haven't got the slightest idea about what a WBS number is??

 

[pjmorris]

Arthur, its a Work Breakdown Structure number.

The OP has presented what on the face of it is pretty straightforward, but is (I think) quite challenging. I'm currently thinking the easiest solution will be a recursive function or PowerQuery. It'll give me something to consider.

 

[suri7891]

Arthur, its a Work Breakdown Structure number.

The OP has presented what on the face of it is pretty straightforward, but is (I think) quite challenging. I'm currently thinking the easiest solution will be a recursive function or PowerQuery. It'll give me something to consider.

Thanks Arthur. Yes, it is WBS no. I am waiting for the formula to apply on my tasks in excel.

 

[Dave Patton]

There was a solution on www about 10 years ago.
The WBS with VBA flowed I think from Excel's indent.
You could check that article (post) and the comments; the approach or ideas may be useful.

search for
Project-Style (WBS) Numbering

 

[pjmorris]

Well, the need to refer to the rows above made the recursive solution impossible. I've settled on a two stage approach:

1. Click in your source column, then chose 'From Table' in the 'Get & Transform' tab of the Data ribbon. In the query editor that then appears split the column containing your tasks/subtasks using '|' as the delimiter. Click 'Close & Load' on the Home ribbon. The revised data will be loaded to a new table. You could split the column using excel, but the advantage of this is it can be automated and indeed can be modified to accept data from a file rather than needing to be embedded in your workbook. (With more effort I might be able to do the whole solution in PowerQuery, but I don't think it would be straightforward.)
2. Apply the formula's as shown below.

Note, I think that, formally, your WBS examples are not rigorous: you change the top level numbering from 0 to 1, when you start to decompose the task.

HTH

Book2.xlsx
	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 Number
2	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)				1				1
3	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services			1	1			1.1
4	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Client Training		1	1	1		1.1.1
5	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Dev Ops		1	1	2		1.1.2
6	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Environment Management		1	1	3		1.1.3
7	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities		1	1	4		1.1.4
8	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Core Bug Fixing	1	1	4	1	1.1.4.1
9	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Environment Management (non-chargeable)	1	1	4	2	1.1.4.2
10	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Internal Training	1	1	4	3	1.1.4.3
11	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	System Testing	1	1	4	4	1.1.4.4
12	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Travel	1	1	4	5	1.1.4.5
13	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services			1	2			1.2
14	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services	Conversion Delivery Management		1	2	1		1.2.1
15	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services	PMO		1	2	2		1.2.2
16	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services	Project Management		1	2	3		1.2.3
Sheet2

Cell Formulas
Range	Formula
E2:E16	E2	=IF([@[Task Name.1]]<>"",IF(A2<>A1,IFERROR(E1+1,1),E1),"")
F2:F16	F2	=IF([@[Task Name.2]]<>"",IF(B2<>B1,IFERROR(F1+1,1),F1),"")
G2:G16	G2	=IF([@[Task Name.3]]<>"",IF(C2<>C1,IFERROR(G1+1,1),G1),"")
H2:H16	H2	=IF([@[Task Name.4]]<>"",IF(D2<>D1,IFERROR(H1+1,1),H1),"")
I2:I16	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	=Sheet2!$A$1:$D$16	E2

 

[suri7891]

Well, the need to refer to the rows above made the recursive solution impossible. I've settled on a two stage approach:

1. Click in your source column, then chose 'From Table' in the 'Get & Transform' tab of the Data ribbon. In the query editor that then appears split the column containing your tasks/subtasks using '|' as the delimiter. Click 'Close & Load' on the Home ribbon. The revised data will be loaded to a new table. You could split the column using excel, but the advantage of this is it can be automated and indeed can be modified to accept data from a file rather than needing to be embedded in your workbook. (With more effort I might be able to do the whole solution in PowerQuery, but I don't think it would be straightforward.)
2. Apply the formula's as shown below.

Note, I think that, formally, your WBS examples are not rigorous: you change the top level numbering from 0 to 1, when you start to decompose the task.

HTH

Book2.xlsx
	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 Number
2	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)				1				1
3	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services			1	1			1.1
4	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Client Training		1	1	1		1.1.1
5	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Dev Ops		1	1	2		1.1.2
6	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Environment Management		1	1	3		1.1.3
7	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities		1	1	4		1.1.4
8	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Core Bug Fixing	1	1	4	1	1.1.4.1
9	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Environment Management (non-chargeable)	1	1	4	2	1.1.4.2
10	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Internal Training	1	1	4	3	1.1.4.3
11	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	System Testing	1	1	4	4	1.1.4.4
12	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Implementation Services	Non-Chargeable Activities	Travel	1	1	4	5	1.1.4.5
13	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services			1	2			1.2
14	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services	Conversion Delivery Management		1	2	1		1.2.1
15	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services	PMO		1	2	2		1.2.2
16	8124 ING Matching SWIFT 2020 Upgrade (REC Upgrade Implementation)	Project Services	Project Management		1	2	3		1.2.3
Sheet2

Cell Formulas
Range	Formula
E2:E16	E2	=IF([@[Task Name.1]]<>"",IF(A2<>A1,IFERROR(E1+1,1),E1),"")
F2:F16	F2	=IF([@[Task Name.2]]<>"",IF(B2<>B1,IFERROR(F1+1,1),F1),"")
G2:G16	G2	=IF([@[Task Name.3]]<>"",IF(C2<>C1,IFERROR(G1+1,1),G1),"")
H2:H16	H2	=IF([@[Task Name.4]]<>"",IF(D2<>D1,IFERROR(H1+1,1),H1),"")
I2:I16	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	=Sheet2!$A$1:$D$16	E2

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]

Hi All,

I got some formula to populate the WBS no. but it is only taking till first "|" symbol as sub task.

Example : In Row no. 8. I have sub task of sub task (Core Bug Fixing). Therefore, the outline number should be 1.4.1. But I am not getting it. Can you one of you complete the below formula considering "|" as sub tasks.

=IF(B4="","",IF(OR(ROW()=2,B3=""),1+INT(MAX(A$1:A3)),A3+0.1))

 

[Dave Patton]

T10_1701a.xlsm
	A	B
1		Task
2	1	ABC
3	1.1	Item 1
4	1.2	Item 2
5	1.2.1	Item 2a
6	1.2.2	Item 2b
7	1.2.2.1	item 2b i
8	1.2.2.2	item 2b ii
9	1.2.2.2.1	item 2c ii a
10	2	DDD
4c

 

[suri7891]

T10_1701a.xlsm
	A	B
1		Task
2	1	ABC
3	1.1	Item 1
4	1.2	Item 2
5	1.2.1	Item 2a
6	1.2.2	Item 2b
7	1.2.2.1	item 2b i
8	1.2.2.2	item 2b ii
9	1.2.2.2.1	item 2c ii a
10	2	DDD
4c

Hi Dave,

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