Comparing 2 columns and concatenating the Data of adjacent column

I have data in Sheet1 with 4 columns: ID, Sequence, DOS, and Note.

My code creates a duplicate sheet and removes columns B and D, keeping only columns A and C. Then, it removes any duplicate entries based on the values in columns A and C.

Next, the code looks up data from Sheet1 using columns A and C as the key, and concatenates the values in column D. It then pastes this data into column C of the duplicate sheet.

I would like to add a few more lines of code to include column 2, Sequence, so that the data is copied based on ascending order of sequence values. However, I'm having difficulty implementing this and would appreciate any assistance.

Sub ConvertRawData()
    Dim result      As String
    Dim lastRow     As Long
    Dim lastRow2    As Long
    Dim i           As Long
    Dim q           As Long
    Dim ws2         As Worksheet
    
    'Duplicate Sheet1 and move it to after Sheet2
    N = Sheets.Count
    Sheets("Sheet1").Copy After:=Sheets(N)
    
    'Remove columns B and D from the duplicated sheet
    ActiveSheet.Range("B:B,D:D").Delete Shift:=xlToLeft
    
    'Remove duplicates from columns A and B in the duplicated sheet
    With ActiveSheet.Range("$A$1:$B$50")
        .RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End With
    
    Set ws2 = ActiveSheet
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
    For q = 2 To lastRow2
        For i = 2 To lastRow
            
            If Sheets("Sheet1").Cells(i, "A") = ws2.Cells(q, "A").Value And _
               Sheets("Sheet1").Cells(i, "C") = ws2.Cells(q, "B").Value Then
            If result <> "" Then
                result = result & " "
            End If
            result = result & Sheets("Sheet1").Cells(i, "D").Value
        End If
    Next i
    ws2.Cells(q, "C").Value = result
    result = ""
Next q
End Sub

Hello, Yes you are right and one more thing is this code takes alot of time on large dataset for processing if you can make its processing more faster i would appreciate.

Using ScreenUpdating and Calculation can save time, but they may not be effective in all situations.

The amount of data being processed varies from week to week, but it will never be less than 50,000 rows.

Thank you very much Mumps

One more favor is it possible to convert the result into below format with dates and brackets

Thank you its working fine but there is one problem that is it should concatenate the Dates and Notes for unique ID. Right now code is concatenating based on ID's and Dates:

For Example for Id# 14 it should concatenate like this in ascending order

I would like to merging data that contains repeating ID's, specifically in cases where a single ID appears multiple times with unique DOS (Date of Service) entries. My objective is to consolidate the notes associated with each unique DOS entry into a single cell, similar to the example I provided earlier.

So all notes and thier respective dates will be merged for each unique ID

sure give me moment please