Generating a Unique, Sequential ID in Excel According to the String Value in Another Column

ponderr16

New Member
Joined
Aug 9, 2021
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
In Column A of my excel sheet I'm trying to create a unique, sequential ID in each row according to the 'activity type' in Column C. Activity Title is in Column B, but the two relevant columns for this example are Column A and Column C. There are three activity types: Milestones, Tasks, and Subtasks.

A 'Milestone' is the largest body of work and can have several tasks and subtasks aligned to it. A 'Task' falls beneath a milestone and can have several subtasks aligned to it. And finally, a 'Subtask' is the smallest body of work and will not have anything aligned to it, but there can be several subtasks. Additionally, there is no limit to the number of 'Tasks' that fall beneath one milestone, and no limit to the number of 'Subtasks' that fall beneath one 'Task.

Thanks to some help, I've successfully formatted Column A using the formula below to show in the format listed below:

Formula:
Excel Formula:
=IFERROR(IF(C3="Milestone",FLOOR(A2,1)+1,IF(C3="Task",FLOOR(A2,0.1)+0.1,A2+0.01)),1)

Output:
ID (Column A)Activity Name (Column B)Activity Type (Column C)
1XMilestone
1.1XTask
1.2XTask
1.21XSubtask
1.22XSubtask
1.3XTask
2XMilestone
2.1XTask
2.11XSubtask
2.12XSubtask


However, I realized that if there are 10+ tasks or subtasks, it would reflect the nearest number (i.e. the task following task ID 1.9 would show as 2, which would imply a new milestone), so I'm hoping to adjust the formula from above and have it reflect a second decimal point to account for 10+ tasks and subtasks (For example 1.11.12). My thinking is that this change would allow for up to 99 tasks and subtasks before it rolls over (and there is no chance there would be that many tasks beneath one milestone or subtasks beneath one task).

I've included an example below for the ideal future state:

ID (Column A)Task Name (Column B)Activity Type (Column C)
1XMilestone
(Intermediary rows of data implied)X...
1.09.09XSubtask
1.09.10XSubtask
1.09.11XSubtask
1.10XTask
1.10.01XSubtask
1.10.02XSubtask
1.11XTask
1.11.01XSubtask


Any help or guidance provided is so appreciated!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Last edited:
Solution

Forum statistics

Threads
1,148,253
Messages
5,745,686
Members
423,968
Latest member
LTCASA

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
Top