+ Reply to Thread
Results 1 to 2 of 2

Getting Pivot Table to Recognize Multiple Values in a Cell

  1. #1
    Registered User
    Join Date
    04-17-2019
    Location
    Michigan
    MS-Off Ver
    Microsoft 2016
    Posts
    1

    Lightbulb Getting Pivot Table to Recognize Multiple Values in a Cell

    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...
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,123

    Re: Getting Pivot Table to Recognize Multiple Values in a Cell

    Don't use a pivot table. Use a list of unique drugs starting in P4, and in Q4, use the formula

    =COUNTIF(E:E,"*"&P4&"*")

    and copy down to match your list of drugs.
    Bernie Deitrick
    Excel MVP 2000-2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1