How to move data from rows to columns

[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?

 

[Jacob Skaria]

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

 

[Rick Rothstein]

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
(e-mail address removed)> wrote in message
news[email protected]...

 

[How to move data from rows to columns]

My data is sorted as shown, but each employee has between 1-5 rows of data.

 

[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]

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

 

[Atishoo]

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