Selecting data from columns and displaying them as rows

[chrisvail]

This may be any easy thing to do but I'm trying to do the following and I
need a very detailed explanation of how to do it. About 1000 employees will
be grouped but their subordinate will need to
be displayed on the same row as the employee with each subordinate in a
different column for each employee.

I trying to convert this type of table:
EMPLOYEE SUBORDINATE
Employee 1 Subordinate A
Employee 1 Subordinate B
Employee 2 Subordinate C
Employee 2 Subordinate D
Employee 3 Subordinate E
Employee 3 Subordinate F

To this type:
EMPLOYEE SUBORDINATE 1 SUBORDINATE 2
Employee 1 Subordinate A Subordinate B
Employee 2 Subordinate C Subordinate D
Employee 3 Subordinate E Subordinate F


Thank you,

Chris

 

[Jim Thomlinson]

What you are asking for requires a macro. Something like this should be
close...

Public Sub TransposeSpecial()
Dim rngEmp As Range
Dim rngAllEmps As Range
Dim wks As Worksheet
Dim wksNew As Worksheet
Dim rngPaste As Range

Set wks = ActiveSheet
Set wksNew = Worksheets.Add
Set rngPaste = wksNew.Range("A1")

With wks
Set rngAllEmps = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
End With

For Each rngEmp In rngAllEmps
If rngEmp.Value <> rngEmp.Offset(-1, 0).Value Then
Set rngPaste = rngPaste.Offset(1, 0)
rngPaste.Value = rngEmp.Value
End If
rngPaste.Offset(0, _
Application.WorksheetFunction.CountA(rngPaste.EntireRow)).Value
= _
rngEmp.Offset(0, 1)
Next rngEmp
End Sub

 

[chrisvail]

Hi Jim,

Thank you! This worked perfectly. Very useful.

Chris