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