Home > Blog

How to make each task unique in a plan

Why?

It is good practice not to have duplicate task names in a plan.  This is because sometimes you will not have the context provided by the summary lines, for instance

  • when looking at a task view, which "Review" is the person doing, or
  • when looking a filtered list of milestones, which "Complete" does this milestone refer to.

Ways to de-duplicate

  • Manually - Go down the plan and make sure that the summary task is reflected in each task.
  • Use Excel - Copy the tasks into Excel, use a Countif statement to identify any duplications and resolve the issue and then copy the tasks back into MSP ensuring that you use Paste Special/text.
  • Or, easiest of all, use a Macro

Below you will find some macros to help with making task names unique.  To use them:

  • Press F11 to display the VBA editor
  • Select your MSP file and Insert / Module
  • Copy the text, ensuring that you copy everything from Sub .... to End Sub inclusive, and paste into the blank module space
  • Run your chosen macro.

The macros provided are:

Macro Name What it does
Task_names_add_text Allows you to either add your chosen text or use the summary line text and add it as a prefix or suffix to each row that has been selected.
task_names_fully_auto_de_dup This goes through the whole plan and checks if there are any duplicates.  If there are it adds the information from the summary row to each task (unless it is already there).  Note that you can choose the level of summary to use; you don't have to use the immediate summary to provide context.
task_names_trim A little macro which trims off any excess spaces around task names.  This is a bonus macro.  It is most useful when you have copied task names in from Excel or somewhere else that has used tabs or spaces to align tasks under summary rows.
Additional functions which are used in the above macros
task_test Checks that a row isn't blank or an external task.
speedup_ON Speeds up the macro by switching off calculation and screen updating.
speedup_OFF Returns MSP to the normal Automatic calculations and screen updating settings when each macro has finished.

 

Macros to copy

Option Compare Text
Sub Task_names_add_text()
'created by Miles Goodchild August 2021
'inspired by macro by Jack Dahlgren Feb 2002

Dim Text_string As String
Dim t As Task
Dim choice As Integer
Dim ignore As String
Dim pre As String


If ActiveSelection = 0 Then
MsgBox "Please select your chosen tasks and re-run this maro"
Exit Sub
End If

use_summary = MsgBox("Do you want to add your own text?" & vbCrLf & "Choose NO to use the existing summary lines", vbQuestion + vbYesNo, "Adding text to many tasks 1/4")

If use_summary = vbYes Then
Text_string = InputBox("Enter the text you want to add, no need to add a space", "Adding text to many tasks 1/4")
If Text_string = "" Then Exit Sub
Else
'use summary lines
End If

choice = InputBox("chose where to add the text." & vbCrLf & "1 = Before (prefix)" & vbCrLf & "2 = After (Sufix)", "Adding text to many tasks 2/4", 2)

ignore = MsgBox("do you want to skip names that already have your new text in them?", vbQuestion + vbYesNo + vbDefaultButton1, "Adding text to many tasks 3/4")
If choice = 1 Or choice = 2 Then
If choice = 1 Then 'choose prefix
pre = InputBox("Choose which prefix you would like." & vbCrLf & "1 = Space" & vbCrLf & "2 = Dash" & vbCrLf & "3 = Colon", "Adding text to many tasks 4/4", 2)
Select Case pre
Case 1
pre = " "
Case 2
pre = " - "
Case 3
pre = ": "
End Select
Else
'chose suffix
pre = InputBox("Choose which suffix you would like." & vbCrLf & "1 = Space" & vbCrLf & "2 = Dash" & vbCrLf & "3 = Brackets", "Adding text to many tasks 4/4", 3)
Select Case pre
Case 1
pre = " "
Case 2
pre = " - "
Case 3
pre = " ("
End Select
End If

For Each t In ActiveSelection.Tasks
If t Is Nothing Then
'do nothing
Else
If t.Summary = True And use_summary = vbNo Then Text_string = t.Name

If InStr(t.Name, Text_string) = 0 Then
If choice = 1 Then t.Name = Text_string & pre & t.Name
If choice = 2 Then
If pre = " (" Then
t.Name = t.Name & pre & Text_string & ")"
Else
t.Name = t.Name & pre & Text_string
End If
End If
Else
'do nothing for this task
End If
End If
Next t
Else
Exit Sub
End If
End Sub
Sub task_names_trim()
Dim t As Task

For Each t In ActiveProject.Tasks
If t Is Nothing Then
'do nothing
Else
t.Name = Trim(t.Name)
End If

Next t

End Sub

Sub task_names_fully_auto_de_dup()
Dim t As Task
Dim t_test As Task
Dim Dups As New Collection
Call speedup_ON

For Each t In ActiveProject.Tasks
If task_test(t) Then 'check the row is valid (not external or blank)
For Each t_test In ActiveProject.Tasks
t.Name = RTrim(t.Name) 'trim any spaces to the right of the name (stops any which look the same but having a space)
If task_test(t_test) Then 'check the row is valid (not external or blank)
'Compare t_test name to t to find dups and add to dups collection
If t_test.Name = t.Name And t_test.ID <> t.ID Then
Dups.Add t.Name 'ideally need to work out how to avoid trying to add the same name more than once
End If
End If
Next t_test
End If
Next t
Dim ref As String
Dim pre As String

If Dups.Count = 0 Then
MsgBox ("No duplicates found")
Call speedup_OFF
Exit Sub
Else
'print out the dups so that we can trouble shoot them (for instance then the dups are at the top level of the WBS and can't be eliminated
Dim dupPrint As Variant
For Each dupPrint In Dups
For Each t In ActiveProject.Tasks
If task_test(t) Then If t.Name = dupPrint Then ref = ref & " - " & t.ID
Next t
Debug.Print dupPrint & ref
ref = ""
Next dupPrint
'offer choices for where the summary names will be added
choice = InputBox("chose where to add the summary names." & vbCrLf & "1 = Before (prefix)" & vbCrLf & "2 = After (Sufix)", "Auto de-duplication of names 1/2", 2)
If choice = 1 Then 'choose prefix
pre = InputBox("Choose which seperator you would like." & vbCrLf & "1 = Space" & vbCrLf & "2 = Dash" & vbCrLf & "3 = Colon", "Adding text to many tasks 2/2", 2)
Select Case pre
Case 1
pre = " "
Case 2
pre = " - "
Case 3
pre = ": "
End Select
Else
'chose suffix
pre = InputBox("Choose which seporator you would like." & vbCrLf & "1 = Space" & vbCrLf & "2 = Dash" & vbCrLf & "3 = Brackets", "Adding text to many tasks 2/2", 3)
Select Case pre
Case 1
pre = " "
Case 2
pre = " - "
Case 3
pre = " ("
End Select
End If
End If


Dim SummaryName As String
Dim WBS_String() As String
Dim Target_WBS As String
Dim t_wbs As Task

Dim summ_choice As String
Dim level_choice As String

summ_choice = InputBox("Do you want the immediate summary name or choose the level of the sumamry?." & vbCrLf & "1 = Choose the level" & vbCrLf & "2 = Immediate Summary", "Auto de-duplication of names 3/4", 2)
level_choice = InputBox("What level of summary do you want to include in the de-duplication name. The top level is 0", "Auto de-duplication of names 3/4", 1)

For Each t In ActiveProject.Tasks
If task_test(t) Then 'checks the row is valid
Dim item As Variant
For Each item In Dups
If t.Name = item Then ' the item is a dup; get the next level up's name
If InStr(1, t.WBS, ".") <> 0 Then 'if this is the top level we can't get a name
WBS_String = Split(t.WBS, ".")
If summ_choice = 2 Then
ReDim Preserve WBS_String(LBound(WBS_String) To UBound(WBS_String) - 1) 'removes the last element of the WBS
Else
If level_choice > UBound(WBS_String) Then level_choice = UBound(WBS_String)
ReDim Preserve WBS_String(level_choice) 'removes all but the first element of the WBS
End If
Target_WBS = Join(WBS_String, ".") 're-join the WBS into the target WBS to find
For Each t_wbs In ActiveProject.Tasks ' find the target WBS and grab the name
If task_test(t_wbs) Then
If t_wbs.WBS = Target_WBS Then SummaryName = t_wbs.Name
End If
Next t_wbs
't.Name = t.Name & " (" & SummaryName & ")" 'add the Summary name to the task
If choice = 1 Then t.Name = SummaryName & pre & t.Name
If choice = 2 Then
If pre = " (" Then
If Len(t.Name & pre & SummaryName & ")") > 255 Then
t.Name = Left(t.Name & pre & SummaryName & ")", 255)
Else
t.Name = t.Name & pre & SummaryName & ")"
End If
Else
If Len(t.Name & pre & SummaryName) > 255 Then
t.Name = Left(t.Name & pre & SummaryName, 255)
Else
t.Name = t.Name & pre & SummaryName
End If
End If
End If
End If
End If
Next item
End If
Next t

MsgBox ("all done")
Call speedup_OFF
Exit Sub

opps:
Call speedup_OFF
MsgBox ("there was an error")

End Sub
Function task_test(t As Task) 'use to replace all the indents
task_test = True
If t Is Nothing Then
task_test = False
Else
If t.ExternalTask = True Then task_test = False
End If

End Function


Sub speedup_ON()
Application.ScreenUpdating = False
Application.Calculation = pjManual

End Sub
Sub speedup_OFF()
Application.ScreenUpdating = True
Application.Calculation = pjAutomatic
End Sub

 

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.