I have a list of Employees and the year they started as follows:

Employee Year
John 1998
Betty 2001
Sam 1985
Joe 1998
Pete 2001

I would like to create a list like this:

1985 1998 2001
Sam John Betty
Joe Pete

I tried to use a Pivot Table, but I cant get the names in each column.

Any suggestions would be appreciated.

Thanks in advance.

Pivottables show numeric summaries--sums, counts, averages...

They can't show text in the details.

But you could use a macro:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim res As Variant
Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
.Range("b1", .Cells(.Rows.Count, "b").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, _
copytorange:=NewWks.Range("a1"), _
unique:=True
End With

With NewWks
With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
.Sort key1:=.Columns(1), order1:=xlAscending, _
header:=xlYes
.Copy
.Range("b1").PasteSpecial Transpose:=True
.Columns(1).Delete
End With
End With

With CurWks

For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
res = Application.Match(.Cells(iRow, "b").Value, _
NewWks.Rows(1), 0)
If IsError(res) Then
'this shouldn't happen!
MsgBox "Error with row #: " & iRow
Else
With NewWks
Set DestCell _
= .Cells(.Rows.Count, res).End(xlUp).Offset(1, 0)
End With
DestCell.Value = .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Big John wrote:

I have a list of Employees and the year they started as follows:

Employee Year
John 1998
Betty 2001
Sam 1985
Joe 1998
Pete 2001

I would like to create a list like this:

1985 1998 2001
Sam John Betty
Joe Pete

I tried to use a Pivot Table, but I cant get the names in each column.

Any suggestions would be appreciated.

Thanks in advance.

--

Dave Peterson