Transpose large data from columns to rows

  • Thread starter Thread starter Klemen25
  • Start date Start date
K

Klemen25

Hello thanks for the help if possible.

I have 2 columns. Column A contains names of employees column B
contains the trainings the employees attended.

Each employee has different number of trainings (I got this data
sorted by simple pivot table)

A B
Employee X training 1
Employee X training 2
Employee X training 3
Employee Y training 3
Employee Y training 2
Employee Z training 5
Employee Z training 7
Employee Z training 3
Employee Z training 1


I would like to somehow change data that the name of the employee
would be in column A, and all his trainings would be moved (instead of
vertically) horizontally (to columns B, C, D,E…). So:

A B
C D E
Employee X training 1 training 2 training
3
Employee Y training 3 training 2
Employee Z training 5 training 7
training 3 training 1

Any ideas how to achieve this? Transpose could be used to simply
change this layout, but the number of total employees is too large so
it would take to long.

Thank you
 
Perhaps more understandable result I seek:

ABCDE
Employee X training 1 training 2 training 3
Employee Y training 3 training 2
Employee Z training 5 training 7 training 3 training 1
 
I managed to find the problem myself, useing pivot table, if forumals
and deleting blank cells. Took me some time, but I did it :)
Thanks
 
Without pivot table

Sub lineemup()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) = Cells(i + 1, 1) Then
nc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
Cells(i + 1, 2).Resize(, nc).Copy Cells(i, nc)
Rows(i + 1).Delete
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hello thanks for the help if possible.

I have 2 columns. Column A contains names of employees column B
contains the trainings the employees attended.

Each employee has different number of trainings (I got this data
sorted by simple pivot table)

A B
Employee X training 1
Employee X training 2
Employee X training 3
Employee Y training 3
Employee Y training 2
Employee Z training 5
Employee Z training 7
Employee Z training 3
Employee Z training 1


I would like to somehow change data that the name of the employee
would be in column A, and all his trainings would be moved (instead of
vertically) horizontally (to columns B, C, D,E�). So:

A B
C D E
Employee X training 1 training 2 training
3
Employee Y training 3 training 2
Employee Z training 5 training 7
training 3 training 1

Any ideas how to achieve this? Transpose could be used to simply
change this layout, but the number of total employees is too large so
it would take to long.

Thank you
 
Back
Top