Excel - transpose multiple records

[trevb]

Can anyone help please, I have data in multiple records that I need to transpoe into single records based on ID as below -

ID Data1 Data2
R1 aaa ss
R1 xxx ff
R1 bbb dd
R2 ddd aaa
R2 bbb dd
R3 aaa bbb
R3 vv xxx

I need to transpose to

ID Data1 Data1 Data1 Data2 Data2 Data2
R! aaa xxx bbb ss ff dd
R2 bbb ddd aaa dd
R3 aaa vv bbb xxx

Any help is much appreciated

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LimitRow As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

LimitRow = i - 1
Do While .Cells(i, "A").Value2 = .Cells(LimitRow, "A").Value2

LimitRow = LimitRow - 1
Loop
LimitRow = LimitRow + 1

If LimitRow <> i Then

For j = i To LimitRow + 1 Step -1

.Cells(LimitRow, 3 + i - j + 1).Insert shift:=xlToRight
.Cells(LimitRow, 3 + i - j + 1).Value2 = .Cells(j, "C").Value2
.Cells(LimitRow, "C").Insert shift:=xlToRight
.Cells(LimitRow, "C").Value2 = .Cells(j, "B").Value2

.Rows(j).Delete
Next j
End If
Next i
End With

End Sub
[/vba]

[trevb]

xld thanks very much but the code missed some data, I have attached the data I tested it on, and the result is in sheet 2

[Bob Phillips]

That is because your example only showed two columns of transposable data, there were no blanks in either, so I coded against the spec.

If the reality is different, you need to explain how the reality should be handled, not some cmpletely different scenario. I am not a mind reader, I can only go against what I see/am told.

[trevb]

Apologies xld I shouild have explained it better, basically there are up to 10 records (rows) against each id with 3 columns of data against each id, I need to transpose the data across the 30 columns but some cells may not have any data, I hope this explains it more clearly

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long, j As Long, k As Long
Dim LimitRow As Long
Dim LastRow As Long
Dim TargetCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

LimitRow = i - 1
Do While .Cells(i, "A").Value2 = .Cells(LimitRow, "A").Value2

LimitRow = LimitRow - 1
Loop
LimitRow = LimitRow + 1

.Rows(LastRow + 1).Insert
.Cells(LastRow + 1, "A").Value2 = .Cells(i, "A").Value2
For k = 1 To 3

For j = LimitRow To i Step 1

If .Cells(j, k + 1).Value2 <> "" Then

TargetCol = .Cells(LastRow + 1, .Columns.Count).End(xlToLeft).Column + 1
.Cells(LastRow + 1, TargetCol).Value2 = .Cells(j, k + 1).Value2
End If
Next j
Next k

i = LimitRow
Next i

.Rows(2).Resize(LastRow - 1).Delete
End With
End Sub
[/vba]

[trevb]

xld many thanks again, is it possible to process the data so that it is transposed as in the attached, allows for blank cells ?

[Bob Phillips]

That is another nuance you didn't tell me before. What more is up your sleeve?

Show me an example where one id has 4 or 5 rows.

[trevb]

xld once again apologies I really appreciate your help, attached is what I am trying to achieve, the data is potentially up 50,000 records (5,000 individuals) that's why I need it automating

[Bob Phillips]

Trev,

I can appreciate that you want to automate a laborious process, but if you want assistance you need to do your part, by stating clearly what the data looks like, and how you want it transformed.

This new example is even more different than before. Taking a simple example you previously said that

ID Data1 Data2
R1 R11a R11b R11c
R1 R12a R12b R12c
R1 R13a R13b R13c
R2 R21a R21b R21c
R2 R22a R22b R22c
R3 R31a R31b R31c
R3 R32a R32b R32c

would transform to

ID Data1 Data1 Data1 Data2 Data2 Data2
R1 R11a R12a R13a R11b R12b R13b R11c R12c R12c
R2 R21a R22a R21b R22b R21c R22c
R3 R31a R32a R31b R32b R32c R32c

Now you seem to be saying that it would be transformed into

ID Data1 Data1 Data1 Data2 Data2 Data2
R1 R11a R11b R11c R12a R12b R12c R13a R13b R13c
R2 R21a R21b R21c R22a R22b R22c
R3 R31a R31b R31c R32a R32b R32c

Setting aside the blanks situation, which is not shown in your new example, before I get out my coding quill I want to confirm which is the correct layout.