View Full Version : Excel - transpose multiple records
trevb
03-30-2010, 12:17 AM
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
03-30-2010, 02:41 AM
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
trevb
03-30-2010, 03:07 AM
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
03-30-2010, 03:30 AM
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
03-30-2010, 03:40 AM
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
03-30-2010, 04:39 AM
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
trevb
03-30-2010, 04:57 AM
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
03-30-2010, 05:25 AM
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
03-31-2010, 12:08 AM
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
03-31-2010, 12:48 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.