pulling column_wise data into single row_wise data?!

[via135]

hi!

in sheet1, i am having codes(header) (numerical/text/alpha_numerical) in col A and values(header) in col B as under:


COL A COL B
[codes] [values]
abc 10
abc -10
1111 20
2222 30
1111 -40
ab123 50
ab123 60
abc 20


now what i want is in sheet2 codes in COL A and values in COL B ,
COL C, COLD ..etc in row wise as under:


COL A COL B COL C COL D
[codes] [value1] [value2] [value3]
abc 10 -10 20
1111 20 -40
2222 30
ab123 50 60


any help pl?


thks


-via135

[mrice]

Pasting the following code into the VBA tab for Sheet2 will regenerate the required layout every time the sheet is activated.

Private Sub Worksheet_Activate()
Cells.Clear
For N = 1 To Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row
    If Application.CountIf(Columns(1), Sheets("Sheet1").Cells(N, 1)) = 0 Then
        If Cells(1, 1) = "" Then
            Cells(1, 1) = Sheets("Sheet1").Cells(N, 1)
            TargetRow = 1
        Else
            Cells(65536, 1).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(N, 1)
            TargetRow = Cells(65536, 1).End(xlUp).Row
        End If
    Else
        TargetRow = Columns(1).Find(Sheets("Sheet1").Cells(N, 1), , xlValues, xlWhole).Row
    End If
    Cells(TargetRow, 256).End(xlToLeft).Offset(0, 1) = Sheets("Sheet1").Cells(N, 2)
Next N
End Sub

[via135]

thks mr martin!
this is what i exactly wanted!
your code works like a charm!

thks again!

-via135