SOLVED
Home

How to count multiple values in a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1014864%22%20slang%3D%22en-US%22%3EHow%20to%20count%20multiple%20values%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014864%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20first%20time%20I%20post!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20do%20the%20following.%20I%20have%20a%20dataset%20and%20one%20of%20the%20fields%20is%20made%20up%20of%20cells%20that%20can%20have%20multiple%20values.%20For%20example%20the%20below.%20We%20have%20cells%20that%20contain%20zero%2C%20one%20or%20multiple%20names%20and%20I%20would%20need%20to%20count%20those%20names%20like%20in%20a%20pivot%20table.%20These%20names%20are%20separated%20by%20an%20%22enter%22.%20In%20the%20example%20below%20I%20put%20the%20desired%20output.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3E2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EClass%201%3C%2FTD%3E%3CTD%3EJohn%3CBR%20%2F%3EMary%3C%2FTD%3E%3CTD%3EMary%3CBR%20%2F%3EJoseph%3CBR%20%2F%3EMatthew%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EClass%202%3C%2FTD%3E%3CTD%3ETheresa%3CBR%20%2F%3EAnthony%3C%2FTD%3E%3CTD%3EMatthew%3CBR%20%2F%3EMargaret%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EClass%203%3C%2FTD%3E%3CTD%3EJohn%3CBR%20%2F%3ETheresa%3C%2FTD%3E%3CTD%3ETheresa%3CBR%20%2F%3EMargaret%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDesired%20output%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ENumber%20of%20Classes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%3C%2FTD%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3E2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJohn%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETheresa%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1014864%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ecount%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivot%20Table%20Default%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014890%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20multiple%20values%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460924%22%20target%3D%22_blank%22%3E%40Ugarte335%3C%2FA%3E%20I%20used%20Powerquery%20to%20massage%20your%20data%20as%20you%20can%20see%20in%20the%20attached%20file.%20From%20the%20green%20table%20it'll%20be%20easy%20to%20create%20your%20pivottable%20report.%20You%20can%20also%20feed%20the%20result%20of%20the%20query%20into%20the%20pivottable%20directly%20without%20using%20the%20intermediate%20green%20table%20of%20course.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015043%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20multiple%20values%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460924%22%20target%3D%22_blank%22%3E%40Ugarte335%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20modified%20the%20Query%20to%20transform%20the%20data%20in%20the%20desired%20format%20which%20you%20showed%20in%20the%20description.%3C%2FP%3E%3CP%3EPlease%20refer%20to%20the%20attached.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20487px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157696i3B481AD9FC7F6BA4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Name%20Count%20By%20Year.jpg%22%20title%3D%22Name%20Count%20By%20Year.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015089%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20multiple%20values%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460924%22%20target%3D%22_blank%22%3E%40Ugarte335%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20for%20the%20collection%2C%20here%20is%20a%20bit%20another%20version%20of%20query%2C%20resulting%20table%20is%20in%20brown.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016628%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20multiple%20values%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016628%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20for%20your%20help%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20tried%20with%20the%20data%20downloaded%20from%20the%20provider%20and%20it%20does%20not%20recognise%20it%20as%20new%20line%20for%20some%20reason.%20The%20delimiter%20with%20the%20data%20I%20provided%20first%20was%20a%20new%20line%20and%20it%20was%20recognised%20by%20power%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20it%20doesn't.%20I%20have%20attached%20the%20spreadsheet%20for%20your%20convenience.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016822%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20multiple%20values%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460924%22%20target%3D%22_blank%22%3E%40Ugarte335%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20works%20with%20your%20file%2C%20please%20checked%20attached.%20I%20converted%20the%20data%20into%20the%20table%20to%20simplify%2C%20by%20that%20doesn't%20matter.%20You%20may%20use%20range%20or%20query%20sheet%20from%20another%20file.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20251px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157842iFF226C26B79FA728%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EGenerated%20script%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22tblNames%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22Name%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.Split(%5BNames%5D%2C%22%23(lf)%22)%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Expanded%20Custom%22%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%22Name%22%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Country%22%2C%20%22Name%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014932%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20multiple%20values%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014932%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20this%20was%20much%20easier%20than%20I%20expected!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

This is the first time I post!

 

I need to do the following. I have a dataset and one of the fields is made up of cells that can have multiple values. For example the below. We have cells that contain zero, one or multiple names and I would need to count those names like in a pivot table. These names are separated by an "enter". In the example below I put the desired output.

 

 

 20192018
Class 1John
Mary
Mary
Joseph
Matthew
Class 2Theresa
Anthony
Matthew
Margaret
Class 3John
Theresa
Theresa
Margaret

 

Desired output:

 

 Number of Classes
Person20192018
John20
Theresa21

 

Thanks in advance!

 

7 Replies
Highlighted
Solution

@Ugarte335 I used Powerquery to massage your data as you can see in the attached file. From the green table it'll be easy to create your pivottable report. You can also feed the result of the query into the pivottable directly without using the intermediate green table of course.

Highlighted

Thank you, this was much easier than I expected!

Highlighted
PowerQuery to the rescue!
Highlighted

@Ugarte335 

I modified the Query to transform the data in the desired format which you showed in the description.

Please refer to the attached.

Name Count By Year.jpg

 

 

 

Highlighted

@Ugarte335 

Just for the collection, here is a bit another version of query, resulting table is in brown.

Highlighted

Thank you so much for your help@Sergei Baklan

 

However, I tried with the data downloaded from the provider and it does not recognise it as new line for some reason. The delimiter with the data I provided first was a new line and it was recognised by power query.

 

Now it doesn't. I have attached the spreadsheet for your convenience.

 

Thanks

Highlighted

@Ugarte335 

It works with your file, please checked attached. I converted the data into the table to simplify, by that doesn't matter. You may use range or query sheet from another file.

image.png

Generated script is

let
    Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
    #"Added Custom" = Table.AddColumn(
        Source,
        "Name",
        each Text.Split([Names],"#(lf)")
    ),
    #"Expanded Custom" = Table.ExpandListColumn(
        #"Added Custom",
        "Name"
    ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Expanded Custom",
        {"Country", "Name"}
    )
in
    #"Removed Other Columns"