pivot to represent multiple occurances as 1, but link to all ocura

[cv8497]

I have a list of data in which I wat to pivot to show only the number of
different occurances in column "A"(this example is two-bub & ubu) but i also
want it to pull in all occurances of column A when i click on that finding in
the pivot table. is that possible?

A B
1 bub 1.25
2 bub 2.25
3 ubu 3.15
4 bub .98
5 ubu 1.25
6 bub .12

 

[Dave Peterson]

Take a look at how Debra Dalgleish approaches it:
http://contextures.com/xlPivot07.html#Unique

 

[Billy Liddel]

Select your data (Data must have a Column Herader) and choose Pivot Table,

Copy your Header into the Row Field and then copy it again into the Value
field.

Right-click the Sheet Tab and choose View Code, then copy the following into
the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngData As Range
Dim rngSelect As Range
Dim c As Variant, Counter As Integer
Dim Addr As String

Set Target = Range("H4:H5") 'Pivot Range change to suit
Set rngData = Range("A1:A7") 'Column A Range, change to suit

If Intersect(Target, ActiveCell) Is Nothing Then
Exit Sub
Else
For Each c In rngData
If c = ActiveCell Then
Counter = Counter + 1
If Counter = 1 Then
Addr = c.Address
Set rngSelect = Range(Addr)
Else
Addr = c.Address
Set rngSelect = Union(rngSelect, Range(Addr))
End If
End If
Next c

End If
rngSelect.Select

End Sub

Now when you select an item from the pivot table all occurences are selected.

HTH
Peter