Sorting data

sidd.iths,

this is not a lot of information, can you share a workbook to illustrate what the data looks like? maybe a sheet that shows current data, and another with expected results?

sidd,

I have tried to make the macro easy to edit in case your example does not use the same columns/rows as original, let me know if you have problems:

Sub sidd_iths()

Dim ws1 As Worksheet 'the sheet with the source data
Dim ws2 As Worksheet 'the new sheet with sorted data
Dim grouped_data 'to break up the information with commas
Dim cols As Long 'how many columns need sorting (A,B,C,etc)
Dim fcol As Long 'the first column with ABC data
Dim rw As Long 'the row with the "A","B","C"

Set ws1 = Worksheets("Sheet1") 'change the sheet name to the one with the original data

'below is the part you need to change to customize for your sheet layout
rw = 3 'the top row with "A" "B" "C" info was 3 in example, change as needed
cols = 3 'change to how many columns have data (3 in example, ABC)
fcol = 2 'this was the column with "A" in example, change as needed
'end of what you need to edit

Set ws2 = Sheets.Add
ws2.Range(Cells(1, 1), Cells(1, cols)).Value = ws1.Range(ws1.Cells(rw, fcol), ws1.Cells(rw, fcol + cols)).Value
For i = 0 To cols
    grouped_data = Split(ws1.Cells(rw + 1, fcol + i), ",")
    For j = 2 To UBound(grouped_data) + 2
        ws2.Cells(j, 1 + i).Value = grouped_data(j - 2)
    Next j
Next i

End Sub

Option Explicit



Sub makeRows()

Dim source As Worksheet
Dim destination As Worksheet
Dim cols As Long, i As Long, j As Long, k As Long
Dim stringArray() As String


'Adjust for you'
Set source = ThisWorkbook.Sheets(1)
Set destination = ThisWorkbook.Sheets(2)
cols = 3
For i = 2 To source.Cells(Rows.Count, 1).End(xlUp).Row
    For j = 1 To cols
        ReDim stringArray(0 To UBound(Split(source.Cells(i, j), ",")))
        stringArray = Split(source.Cells(i, j), ",")
        For k = 0 To UBound(stringArray)
            destination.Cells(Rows.Count, j).End(xlUp).Offset(1, 0) = stringArray(k)
        Next
    Next j
Next i

End Sub

See attached: