I have managed to make something (although it probably isn't the most efficient way to do it) to do what I needed. I made some macros then use a total of 3 sheets (as it makes it easier for me to run through things) as follows:

Sheet1 - for this example this is where the unquie IDs will be added

Snippet of Sheet1

Sheet2 - this is a sheet that identifies if the unquie ID has already been added to Sheet3 and has an additional column that just presents today's date.

Snippet of Sheet2

Sheet3 - this is the sheet that stores the unique IDs and the date in which they were added to the report.

Snippet of Sheet3

On the Sheet1 as it uses a table and sometimes the data copied over has less than what is there before I made a macro to help clear it after a message box prompt when selecting the first cell where the data would be pasted:

Message box code:

 Sub YesNoMessageBoxCT()
    Dim resp As VbMsgBoxResult
    
    resp = MsgBox("Is new data being added/table being updated? If so please clear table.", vbYesNo)
    Const sName As String = "OMData"
    
    If resp = vbYes Then
        CTA2
    Else
        Exit Sub
    End If
    
End Sub

CTA2 Macro which deletes everything but keeps first 2 rows to for the table format, it also updates the helper cells on Sheet1:

Sub CTA2()
    Const sName As String = "Sheet1
    
    Dim lR As Long
    Sheets(sName).Range("N1").Value = "No"
    Sheets(sName).Range("A2").ClearContents
    Sheets(sName).Range("B2").ClearContents
    Sheets(sName).Range("C2").ClearContents
    Sheets(sName).Range("D2").ClearContents
    
    lR = Sheets(sName).Range("A" & Rows.Count).End(xlUp).Row
    Sheets(sName).Range("A3:A" & lR).EntireRow.Delete
    
    Sheets(sName).Range("N1").Value = "Yes"
End Sub

I included some code on the desired sheet so that whenever there change to Column A (and the two cells I used to help idenifty if the macro needed running or not were correct) to call the macro:

Private Sub worksheet_change(ByVal Target As Range)
    Const sName As String = "Sheet1"
    
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If ThisWorkbook.Sheets(sName).Range("K1").Value > 1 And ThisWorkbook.Sheets(sName).Range("N1").Value = "Yes" Then
            CopyUniqueIDs
        End If
    End If
    
End Sub

So the macro for CopyUniqueIDs is the following:

Sub CopyUniqueIDs()
    Const sName As String = "Sheet1"
    Const dName As String = "Sheet2"
    
    'copy from sName sheet
    Sheets(sName).Range("A:A").Copy
    'Paste data to correct sheet
    Sheets(dName).Range("A:A").PasteSpecial xlPasteValues
    
    'Turn off copy mode
    Application.CutCopyMode = False
    
    MatchAndMove
    
End Sub

This just copies the whole of column A and pastes the values onto another sheet (Sheet2 for example). It then calls MatchAndMove which has the following code:

Sub MatchAndMove()
    
    Const sName As String = "Sheet2"
    Const dName As String = "Sheet3"
    
    Dim lSR As Long ' last source row
    Dim i As Long 'counter
    Dim lDR As Long ' last destination row
    Dim bDR As Long ' blank destination row
    
    With Sheets(sName)
        lSR = .Range("B" & Rows.Count).End(xlUp).Row
        
        For i = 2 To lSR
        lDR = Sheets(dName).Range("A" & Rows.Count).End(xlUp).Row 'gets last row on destination sheet
        bDR = lDR + 1 ' last destination row + 1
            With .Range("B" & i)
                If .Value = "No" Then ' Check if Match is no
                    If IsEmpty(Sheets(sName).Range("A" & i).Value) Or Sheets(sName).Range("A" & i).Value = 0 Then 'stop at blank cell
                        Exit Sub
                    End If
                    Sheets(sName).Range("A" & i).Copy Destination:=Sheets(dName).Range("A" & bDR) 'copy ID to correct sheet
                    Sheets(sName).Range("C" & i).Copy 'copy todays date
                    Sheets(dName).Range("B" & bDR).PasteSpecial xlPasteValues 'paste as value (number), cell formatted to show short date
                End If
            End With
        Next i
        
     End With
    
End Sub

This then checks if each row on Colum A for Sheet2 to see if there is an ID stored and to see if it already exists on Sheet3. If the indicator is "No" it will then copy and past the ID number and copy and paste the value of todays date. Tried to get it to just write the date without the copy and pasting but kept running into problems so this was the easiest solution.

So at the end of it Sheet3 has a record of every ID that has been added to the report and the date in which it was added. For the actual report sheet (not mentioned above) it can now just do a simple VLOOKUP to find the date and present it alongside the correct ID number and will automatically change when the ID moves around the report.

Sorry if both my question or answer is not explained well. I am trying to get better at explaining what I mean.