Help converting row data to column format

Fishin33 · Jan 22, 2007

If I have a list that has a row for every product an employee has, how can I convert that to one per emloyee and there are multiple columns that contain their products.

Example

1234 Prod1
1234 Prod2
1234 Prod3
1235 Prod1
1235 Prod2
1235 Prod3
1236 Prod1
1236 Prod2

Convert to:

1234 Prod1 Prod2 Prod3
1235 Prod1 Prod2 Prod3
1236 Prod1 Prod2

Paste Special works, but only if I manually select one group of rows for the same emloyee. Is there a formula or macro I can do for this?

Thanks!!!!

Russ

P Sitaram · Jan 22, 2007

Code:

Sub CopyToCols()

    Dim rgCell As Range
    Dim i As Integer
    i = 1
    Set rgCell = [a1]
    If rgCell = "" Then Exit Sub
    If rgCell.Offset(1, 0) = "" Then Exit Sub
    Do
        If rgCell = rgCell.Offset(i, 0) Then
            rgCell.Offset(i, 1).Copy rgCell.End(xlToRight).Offset(0, 1)
            rgCell.Offset(i, 1).EntireRow.Delete
            If rgCell.Offset(1, 0) = "" Then
                Exit Sub
            End If
        Else
            Set rgCell = rgCell.Offset(1, 0)
        End If
    Loop
            
    
End Sub

Change [a1] to the starting cell ref

Fishin33 · Jan 22, 2007

You are a genius!!!!!

Thank you so much for the help. Have a great day!

Help converting row data to column format

Fishin33

New Member

Excel Facts

P Sitaram

Well-known Member

Fishin33

New Member

Similar threads

Forum statistics

Share this page

Help converting row data to column format

Fishin33

New Member

Excel Facts

P Sitaram

Well-known Member

Fishin33

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock