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 here:
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.)
