If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
pivot to represent multiple occurances as 1, but link to all ocura
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 |
#2
|
|||
|
|||
pivot to represent multiple occurances as 1, but link to all ocura
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 |
#3
|
|||
|
|||
pivot to represent multiple occurances as 1, but link to all ocura
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 |
Thread Tools | |
Display Modes | |
|
|