Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose data macro
I have a dataset with multiple records per ID:
ID date test result 1 1 1 2 2 2 3 3 I want to make it into a dataset like this so that there are unique IDs: ID date1 test1 result1 date2 test2 result2 1 2 3 etc. I have this macro that does it for ID, date and test, but I want to add result. How do I do that by adding to this macro? ------------------------------ Sub TransposeIt() Dim arr As Variant Dim r As Long Dim Counter As Long Dim ID As String Dim DestRow As Long [a1].Sort Key1:=[a1], Order1:=xlAscending, Key2:=[b1], Order2:=xlAscending, Header:=xlYes arr = ActiveSheet.UsedRange.Value Worksheets.Add [a1] = "ID" DestRow = 1 For r = 2 To UBound(arr, 1) If arr(r, 1) < ID Then DestRow = DestRow + 1 Cells(DestRow, 1) = arr(r, 1) ID = arr(r, 1) Counter = 0 End If Counter = Counter + 1 Cells(1, Counter * 2) = "Date" & Counter Cells(1, Counter * 2 + 1) = "Test" & Counter Cells(DestRow, Counter * 2) = arr(r, 2) Cells(DestRow, Counter * 2 + 1) = arr(r, 3) Next MsgBox "Done" End Sub ------------------------------------------------------ Any help would be much appreciated!!! Pauline |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I group and transpose data - macro help needed. | Excel Worksheet Functions | |||
Transpose macro | Excel Programming | |||
Macro to transpose data to fill blank cells in table | Excel Programming | |||
Help with macro to transpose data | Excel Programming |