Forum Discussion
Drop Down Multiple Selection Analysis Pivot Table
Hoping someone can help.
I have created a drop down list with multiple selection option through VBA. I would like to analyze this data, however, when I insert a pivot table it groups options together.
For example, if in one cell I choose the following options based on data validation - "banana, apple, pear" , another cell - "banana" , and another cell "banana, watermelon" and I ask the pivot table to tell me how many times "banana" was selected, it will only say it was selected once. It treats "banana, apple pear" or "banana, watermelon" as one analysis point.
Is there a way to adjust the VBA code or go about things differently, so that I might be able to treat each selection into a cell as its own value? Or a different way to go about analyzing the data?
Let me know if any clarification is neccesary!
Thanks.
3 Replies
Let me know if any clarification is neccesary!
What do you think? 50 views as of this writing and no responses. I suspect that's a hint that maybe quite a bit of clarification is needed.
Ideally, post a copy of the actual workbook where this is an issue--or a dummy mockup that replicates the real (I'm guessing that your bananas example is just that: an example).... but help us see with more than words what it is that you're working with. That would make it easier to respond with something of substance.
And maybe even describe the context in which this is arising, to the extent that can be done without violating confidentiality or the like.
Post your actual workbook (or mockup) on OneDrive or GoogleDrive with a link pasted here that grants access.
Here is a link anyone should be able to have access to and download, to help understand my issue: https://drive.google.com/file/d/1jmLdE10piAyI3eWAojU2o4Yt6F5yKJUJ/view?usp=sharing.
The first sheet is a multiple drop down using this VBA code:
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
DelimiterType = vbCrLf
If Destination.Count > 1 Then Exit SubOn Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitErrorIf rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue = "" Then
'do nothing
Else
If newValue = "" Then
'do nothing
Else
Destination.Value = oldValue & DelimiterType & newValue
' add new value with delimiter
End If
End If
End IfexitError:
Application.EnableEvents = True
End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
The second sheet is trying to use the sheet 1 table as a base for pivot table analysis. I explain there that I would like the selections to be listed as separate. I might be way in over my head with this, but figure it was worth a shot posting here in case there is a relatively easy fix.
I am certainly open to hearing about other ways to possibly analyze the data that isn't a pivot table - but all I really want to know (based on the mock) is: how many people selected "orange" in the multi-drop down? In reality, I have a much larger dataset where multi-drop down is very useful.
Thanks for any help!
The entries in your spreadsheet, created by that VBA routine. show as
Multiple response to a drop down can't be mixed in one cell; there can, however, be multiple rows, with the person's name appearing multiple times, once for each selection, (in any sequence). With the data arranged that way, Pivot Table correctly summarizes.
So I guess the question to you--assuming you want to use VBA to populate that sheet of selections--can the VBA routine be modified to create it differently?
