Log In
Register
Home
New Member
April 18, 2019
Offline
I document infections in a spreadsheet and some patients take more than 1 antibiotic. I need to find a way to get the pivot table to recognize all of the medications in the column, including those cells with multiple values, and individuals.
I attached a brief version of the file and a picture of the Pivot Table I'm trying to create... I know that the pivot table can't automatically do this, but there has to be a roundabout way to do this. I've tried some things that delineated the cell, but then my other pivot tables will be inaccurate because it will show more values that there actually is...
July 16, 2010
Offline
Hi Jynell,
The only way to do this is to split the 'main antibiotic' data into separate rows, which you can do with Power Query (see file attached). It looks like you need to also unpivot the monthly data that is in hidden columns D:M, as this should be in a single column with another column for the month label for use in a PivotTable.
If splitting the antibiotic data into separate rows messes up your other PivotTables then it might be that you just need to approach them differently, or you'll need two versions of your data, one for the antibiotic count and one for your other PivotTables.
Mynda
1 Guest(s)
