Excel - Analyzing / Counting the comma separated values in cells

Question

Here's an Excel sheet, where we track Demand of Products across the countries [Sheet/Table name: Data]

Country	Products
India	A
Australia	A,B
Brazil	B, C

This Data will be used to understand the demand of the products across the countries, by simply counting products for each country. This is how the data will look like:

Products	Demand
A	2
B	2
C	1

[Sheet/Table name: Product-Demand]

One of the ways, I was able to do this was : Split the comma separated values in the Products Column/cell, and then CountIFS However, this approach involved

1. Every time the Data table was updated, manually copy-pasting this to another sheet.
2. The products may increase, ex: new product "Z" may be launched
3. We use Power BI to create a heat-map of Products in demand across the country. The Power BI Service pulls the data every Monday. Manual effort kind of destroys the automation.

Please advise/guide on

What's the best way to count the products (or comma separated values) in a cell with least amount of manual work.

Thanks!

Answer 1

You need to transform the data into a more useful structure. One way to do that is to use Power Query. Start with your source data as a table called Table1. Then, paste the following code in the Advanced Editor in Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Products", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Products"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Products", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Products.1", "Products.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products.1", type text}, {"Products.2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Country"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

This code will produce a table of usefully-structured data. The final table you want can be produced by a PivotTable using Power Query's output table as a source.

Answer 2

Where the data is in columns A and B on both sheets, and there is a header row with the data starting in row 2, on the Product-Demand sheet, in cell B2, place this formula:

=COUNTIF(Data!$B$2:$B$4,"*" & A2 & "*")

then drag down