columns to rows
I have a file with a great deal of data (though my data will not exced the
limit once arranged in rows) that I need to transpose from column to row based on column A. Data is arranged as follows: ID Day Pct Day Pct Day Pct Day Pct 55462 Monday 45 Tuesday 55 135464 Friday 20 Wednesday 20 Thursday 60 15448 Thurs 20 Monday 20 Friday 20 Wednesday 40 1487 Monday 30 Tuesday 20 0PA 50 My goal: ID Day Pct 55462 Monday 45 55462 Tuesday 55 135464 Friday 20 135464 Wednesday 20 135464 Thursday 60 15448 Thurs 20 15448 Monday 20 15448 Friday 20 15448 Wednesday 40 1487 Monday 30 1487 Tuesday 20 1487 Saturday 50 Any assistance is appreciated. |
columns to rows
One way is to use a macro:
Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 3).Value _ = Array("ID", "Day", "Pct") oRow = 1 With CurWks FirstRow = 2 'headers in 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column For iCol = FirstCol To LastCol Step 2 oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Resize(1, 2).Value _ = .Cells(iRow, iCol).Resize(1, 2).Value Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm excelmad wrote: I have a file with a great deal of data (though my data will not exced the limit once arranged in rows) that I need to transpose from column to row based on column A. Data is arranged as follows: ID Day Pct Day Pct Day Pct Day Pct 55462 Monday 45 Tuesday 55 135464 Friday 20 Wednesday 20 Thursday 60 15448 Thurs 20 Monday 20 Friday 20 Wednesday 40 1487 Monday 30 Tuesday 20 0PA 50 My goal: ID Day Pct 55462 Monday 45 55462 Tuesday 55 135464 Friday 20 135464 Wednesday 20 135464 Thursday 60 15448 Thurs 20 15448 Monday 20 15448 Friday 20 15448 Wednesday 40 1487 Monday 30 1487 Tuesday 20 1487 Saturday 50 Any assistance is appreciated. -- Dave Peterson |
All times are GMT +1. The time now is 05:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com