Project Plan in excel. Unique ID requirement

[theguyIsHere]

I can't use project plan, which will obviously provide an easier fix for this

I a have a simple list of IDs and task names (pls see table below). Requirements are:

• Auto create a unique ID, which could be any number with a prefix from column C. How?
• Once a unique ID is defined it should not change against that task name. How do I detect changes if someone does change the ID cell against an existing task?
• New Unique ID to be assigned when a new line is inserted (with a prefix from column C)
  • Note: New line could be inserted at the bottom or in the middle of the existing list

Happy to use power query, VBA or a semi manual way of managing this process. Any guidance much appreciated

ID	Task Name
1	Task 1
2	Task 2
3	Task 3
4	Task 4

 

[mumps]

Could you post some data that includes column C and some examples of what the unique ID's would look like?

 

[theguyIsHere]

Of course. Does the attached help?

 

[mumps]

Will the prefixes in column C be unique or can there be duplicate prefixes in the column? Do the numbers to be part of the ID have to be unique?

 

[theguyIsHere]

Will the prefixes in column C be unique or can there be duplicate prefixes in the column? Do the numbers to be part of the ID have to be unique?

These will not be unique.

And yes the numbers need to be unique please

 

[theguyIsHere]

Any thoughts on this guys? Thank you

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Start with a blank sheet with only the headers in row 1. Always enter data starting with the Task Name in column B and then the prefix in column C. After entering the prefix, press the RETURN key and the unique ID will be created automatically.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target <> "" Then
        MsgBox ("This ID cannot be changed.")
        Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Dim i As Long, v As Variant, x As Long, lRow As Long
    Dim max As Long, min As Long, key As Variant
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    If Range("A2") = "" Then
        Range("A2") = Target & "-" & 1
    Else
        If lRow > 2 Then
            v = Range("A2:A" & lRow).Value
        Else
            v = Range("A2:A" & lRow + 1).Value
        End If
        Set dic = CreateObject("Scripting.Dictionary")
        For i = LBound(v) To UBound(v)
            If v(i, 1) <> "" Then
                dic.Add i, Split(v(i, 1), "-")(1)
            End If
        Next i
        x = Application.WorksheetFunction.max(dic.keys)
        If Target.Row = lRow + 1 Then
            Target.Offset(, -2) = Target & "-" & x + 1
        Else
            Target.Offset(, -2) = Target & "-" & x
        End If
    End If
End Sub

 

[theguyIsHere]

Thanks

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Start with a blank sheet with only the headers in row 1. Always enter data starting with the Task Name in column B and then the prefix in column C. After entering the prefix, press the RETURN key and the unique ID will be created automatically.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target <> "" Then
        MsgBox ("This ID cannot be changed.")
        Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Dim i As Long, v As Variant, x As Long, lRow As Long
    Dim max As Long, min As Long, key As Variant
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    If Range("A2") = "" Then
        Range("A2") = Target & "-" & 1
    Else
        If lRow > 2 Then
            v = Range("A2:A" & lRow).Value
        Else
            v = Range("A2:A" & lRow + 1).Value
        End If
        Set dic = CreateObject("Scripting.Dictionary")
        For i = LBound(v) To UBound(v)
            If v(i, 1) <> "" Then
                dic.Add i, Split(v(i, 1), "-")(1)
            End If
        Next i
        x = Application.WorksheetFunction.max(dic.keys)
        If Target.Row = lRow + 1 Then
            Target.Offset(, -2) = Target & "-" & x + 1
        Else
            Target.Offset(, -2) = Target & "-" & x
        End If
    End If
End Sub

Thank you for taking the time, firstly!

It kind of half works. So I can still add a duplicate in between lines

I can also add a duplicate at the bottom of column A

Both give error messages and/or take me to the code itself but the end result is that I can add a duplicate, unfortunatley.

 

[mumps]

I can also add a duplicate at the bottom of column A

Can you please clarify what you mean by this?