MS Excel : Transposing columns to avoid redundanci

Question

I have an excel sheet with 2 columns as below:

IMPORT_ID   EXPORT_ID
SI1313721   SI1440839
SI1313721   SI1440997
SI1313722   SI1440672
SI1313722   SI1440776
SI1313722   SI1441313

IMPORT_IDs are getting repeated (SI1313721 - 2 times and SI1313722 - 3 times). I want to transpose this sheet into

SI1313721   SI1440839   SI1440997
SI1313722   SI1440672   SI1440776  SI1441313

How do I do that in Excel probably with vba?

Answer 1

I'm not sure if the order that they are stacked on the right matters but you could try this.

Sub TransposingColumns_to_Avoid_redundancies()
    Dim rw As Long, p As Long
    With ActiveSheet    '<- set this worksheet reference properly!
        For rw = .Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
            If CBool(Application.CountIf(.Cells(1, 1).Resize(rw - 1), .Cells(rw, 1).Value2)) Then
                p = Application.Match(.Cells(rw, 1).Value2, .Cells(1, 1).Resize(rw - 1), 0)
                .Cells(p, Columns.Count).End(xlToLeft).Offset(0, 1) = .Cells(rw, 2).Value2
                .Rows(rw).EntireRow.Delete
            End If
        Next rw
    End With
End Sub

The rows are deleted as the values from column B are stacked to the right. It is usually better to start at the bottom and work up when deleting rows.