Prev: Pivot Table Field Settings
Next: Date changes in data
From: cv8497 on 28 May 2010 10:44
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

From: Dave Peterson on 28 May 2010 11:52
Take a look at how Debra Dalgleish approaches it:
http://contextures.com/xlPivot07.html#Unique

cv8497 wrote:
>
> 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
From: Billy Liddel on 28 May 2010 16:05
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
"cv8497" wrote:

> 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
>
 | 
Pages: 1
Prev: Pivot Table Field Settings
Next: Date changes in data