Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to move data from rows to columns
I am trying to move multiple rows of data to columns.
For example, I have multiples rows of different data for employee A and want one row for employee A that has the multiple rows of data in columns instead. Currently I have: Employee A, dependent 1, birth date Employee A, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date Employee C, dependent 2, birth date Employee C, dependent 3, birth date And I want Employee A, dependent 1, birth date, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date, dependent 2, birth date, dependent 3, birth date I've tried transpose (but have too many columns) and pivot tables, but haven't been able to get anything to work. Can you help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to move data from rows to columns
You can try out the below macro. If you are new to macros..
--Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() PS: lngSRow = 2 is the row number in which your data starts...Change that to 1 if your data do not have headers....Take a backup of your data and try. Sub MyMacro() Dim lngRow As Long, lngFRow As Long, lngCol As Long, lngGRow As Long lngSRow = 2: lngGRow = lngSRow: lngCol = 4: lngRow = lngSRow Do: lngRow = lngRow + 1 If Range("A" & lngRow) = Range("A" & lngRow - 1) Then Cells(lngGRow, lngCol) = Range("B" & lngRow) Cells(lngGRow, lngCol + 1) = Range("C" & lngRow) Rows(lngRow).Delete: lngCol = lngCol + 2: lngRow = lngRow - 1 Else lngGRow = lngRow: lngCol = 4 End If Loop Until Range("A" & lngRow + 1) = "" End Sub If this post helps click Yes --------------- Jacob Skaria "How to move data from rows to columns" wrote: I am trying to move multiple rows of data to columns. For example, I have multiples rows of different data for employee A and want one row for employee A that has the multiple rows of data in columns instead. Currently I have: Employee A, dependent 1, birth date Employee A, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date Employee C, dependent 2, birth date Employee C, dependent 3, birth date And I want Employee A, dependent 1, birth date, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date, dependent 2, birth date, dependent 3, birth date I've tried transpose (but have too many columns) and pivot tables, but haven't been able to get anything to work. Can you help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to move data from rows to columns
My data is sorted as shown, but each employee has between 1-5 rows of data.
"Rick Rothstein" wrote: Is your data sorted by (assumed) Column A (the employee column) as shown, or could the employees be scattered all about in that column? -- Rick (MVP - Excel) "How to move data from rows to columns" <How to move data from rows to wrote in message ... I am trying to move multiple rows of data to columns. For example, I have multiples rows of different data for employee A and want one row for employee A that has the multiple rows of data in columns instead. Currently I have: Employee A, dependent 1, birth date Employee A, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date Employee C, dependent 2, birth date Employee C, dependent 3, birth date And I want Employee A, dependent 1, birth date, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date, dependent 2, birth date, dependent 3, birth date I've tried transpose (but have too many columns) and pivot tables, but haven't been able to get anything to work. Can you help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to move data from rows to columns
This is great!! I need a bit more help--I actually have 16 columns of data
that I need to move, not just the 2 in my post. How do I add that to the macro? Thanks!! "Jacob Skaria" wrote: You can try out the below macro. If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() PS: lngSRow = 2 is the row number in which your data starts...Change that to 1 if your data do not have headers....Take a backup of your data and try. Sub MyMacro() Dim lngRow As Long, lngFRow As Long, lngCol As Long, lngGRow As Long lngSRow = 2: lngGRow = lngSRow: lngCol = 4: lngRow = lngSRow Do: lngRow = lngRow + 1 If Range("A" & lngRow) = Range("A" & lngRow - 1) Then Cells(lngGRow, lngCol) = Range("B" & lngRow) Cells(lngGRow, lngCol + 1) = Range("C" & lngRow) Rows(lngRow).Delete: lngCol = lngCol + 2: lngRow = lngRow - 1 Else lngGRow = lngRow: lngCol = 4 End If Loop Until Range("A" & lngRow + 1) = "" End Sub If this post helps click Yes --------------- Jacob Skaria "How to move data from rows to columns" wrote: I am trying to move multiple rows of data to columns. For example, I have multiples rows of different data for employee A and want one row for employee A that has the multiple rows of data in columns instead. Currently I have: Employee A, dependent 1, birth date Employee A, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date Employee C, dependent 2, birth date Employee C, dependent 3, birth date And I want Employee A, dependent 1, birth date, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date, dependent 2, birth date, dependent 3, birth date I've tried transpose (but have too many columns) and pivot tables, but haven't been able to get anything to work. Can you help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to move data from rows to columns
Try the below..Adjust the starting row and number of columns
lngSRow = 1: lngCols = 5 Sub TranposeData() 'Jacob Skaria -transpose004 Dim lngRow As Long, lngFRow As Long, lngCol As Long, lngGRow As Long lngSRow = 1: lngCols = 5 lngGRow = lngSRow: lngCol = lngCols + 1: lngRow = lngSRow Do: lngRow = lngRow + 1 If Range("A" & lngRow) = Range("A" & lngRow - 1) Then Cells(lngGRow, lngCol).Resize(1, lngCols - 1) = _ Range("B" & lngRow).Resize(1, lngCols - 1).Value Rows(lngRow).Delete: lngCol = lngCol + (lngCols - 1): lngRow = lngRow - 1 Else lngGRow = lngRow: lngCol = lngCols + 1 End If Loop Until Range("A" & lngRow + 1) = "" End Sub If this post helps click Yes --------------- Jacob Skaria "How to move data from rows to columns" wrote: This is great!! I need a bit more help--I actually have 16 columns of data that I need to move, not just the 2 in my post. How do I add that to the macro? Thanks!! "Jacob Skaria" wrote: You can try out the below macro. If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() PS: lngSRow = 2 is the row number in which your data starts...Change that to 1 if your data do not have headers....Take a backup of your data and try. Sub MyMacro() Dim lngRow As Long, lngFRow As Long, lngCol As Long, lngGRow As Long lngSRow = 2: lngGRow = lngSRow: lngCol = 4: lngRow = lngSRow Do: lngRow = lngRow + 1 If Range("A" & lngRow) = Range("A" & lngRow - 1) Then Cells(lngGRow, lngCol) = Range("B" & lngRow) Cells(lngGRow, lngCol + 1) = Range("C" & lngRow) Rows(lngRow).Delete: lngCol = lngCol + 2: lngRow = lngRow - 1 Else lngGRow = lngRow: lngCol = 4 End If Loop Until Range("A" & lngRow + 1) = "" End Sub If this post helps click Yes --------------- Jacob Skaria "How to move data from rows to columns" wrote: I am trying to move multiple rows of data to columns. For example, I have multiples rows of different data for employee A and want one row for employee A that has the multiple rows of data in columns instead. Currently I have: Employee A, dependent 1, birth date Employee A, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date Employee C, dependent 2, birth date Employee C, dependent 3, birth date And I want Employee A, dependent 1, birth date, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date, dependent 2, birth date, dependent 3, birth date I've tried transpose (but have too many columns) and pivot tables, but haven't been able to get anything to work. Can you help? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to move data from rows to columns
I use a very simple sub
For Each c In Worksheets("sheet1").Range("a1:z100") d = c.Row - 1 e = c.Column - 1 Worksheets("sheet2").Range("a1").Offset(e, d) = c.Value Next You can change the ranges to shift the data position. this places the repositioned data on sheet2 but youu can always delete sheet1 and rename sheet2 "How to move data from rows to columns" wrote: My data is sorted as shown, but each employee has between 1-5 rows of data. "Rick Rothstein" wrote: Is your data sorted by (assumed) Column A (the employee column) as shown, or could the employees be scattered all about in that column? -- Rick (MVP - Excel) "How to move data from rows to columns" <How to move data from rows to wrote in message ... I am trying to move multiple rows of data to columns. For example, I have multiples rows of different data for employee A and want one row for employee A that has the multiple rows of data in columns instead. Currently I have: Employee A, dependent 1, birth date Employee A, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date Employee C, dependent 2, birth date Employee C, dependent 3, birth date And I want Employee A, dependent 1, birth date, dependent 2, birth date Employee B, dependent 1, birth date Employee C, dependent 1, birth date, dependent 2, birth date, dependent 3, birth date I've tried transpose (but have too many columns) and pivot tables, but haven't been able to get anything to work. Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move data that occupies multiple rows/columns into one row | Excel Discussion (Misc queries) | |||
Mass move data in rows to columns. | Excel Discussion (Misc queries) | |||
how do i automatically move data from rows to columns? | Excel Discussion (Misc queries) | |||
how do I move data in prexisting rows to columns? | Excel Worksheet Functions | |||
Move Data from rows to columns... | Excel Worksheet Functions |