pivot table from columns with strings separated by commas

[tstimple]

Hello all,
I am importing some data into Excel. One of the columns contains text strings separated by commas. However, the strings that come in are not always in the same order. Here are examples:

COLUMN A
choice1, choice3
choice2, choice5, choice1
choice4
choice1, choice3, choice6

(in reality the strings have multiple words separated by spaces, but commas are only used as a delimiter)

A normal pivot table sees each cell as a single (different) string and therefore counts each as "1"

What i need to do is be able to get a TOTAL count of each individual choice like This:
Choice1 = 3
Choice2 = 1
Choice3 = 2
...etc

How can I select all the cells in the column and derive this result?
Help please.

Tim

 

[tstimple]

Hello all,
I am importing some data into Excel. One of the columns contains text strings separated by commas. However, the strings that come in are not always in the same order. Here are examples:

COLUMN A
choice1, choice3
choice2, choice5, choice1
choice4
choice1, choice3, choice6

(in reality the strings have multiple words separated by spaces, but commas are only used as a delimiter)

A normal pivot table sees each cell as a single (different) string and therefore counts each as "1"

What i need to do is be able to get a TOTAL count of each individual choice like This:
Choice1 = 3
Choice2 = 1
Choice3 = 2
...etc

How can I select all the cells in the column and derive this result?
Help please.

Tim

BTW.. CountIf() is not practical as there are dozens of "Choices" and thousands of rows in the table.
additionally, the table is regularly updated (appended).
Thanks again for any help.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(d,FILTER(A2:A10000,A2:A10000<>""),e,DROP(REDUCE("",d,LAMBDA(x,y,VSTACK(x,TRIM(TEXTSPLIT(y,,","))))),1),u,UNIQUE(e),HSTACK(u,MAP(u,LAMBDA(m,SUM(--(e=m))))))

 

[RoryA]

You could use Power Query to parse the data into separate rows. You can either load it to another table or do it as connection only and use that as the source for your pivot table.

 

[tstimple]

Hi & welcome to MrExcel.
How about

=LET(d,FILTER(A2:A10000,A2:A10000<>""),e,DROP(REDUCE("",d,LAMBDA(x,y,VSTACK(x,TRIM(TEXTSPLIT(y,,","))))),1),u,UNIQUE(e),HSTACK(u,MAP(u,LAMBDA(m,SUM(--(e=m))))))

Fluff!!! That is one heck of a formula! and it Works!!!!
The only issue I had was that the cell I was placing it had been previously formatted as text, and so the formula was not running. But I ran it through chatGPT and it suggested to check the cell formatting and BINGO!
Thank you again for a truly awesome formula!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Grumbledore]

Hi & welcome to MrExcel.
How about

=LET(d,FILTER(A2:A10000,A2:A10000<>""),e,DROP(REDUCE("",d,LAMBDA(x,y,VSTACK(x,TRIM(TEXTSPLIT(y,,","))))),1),u,UNIQUE(e),HSTACK(u,MAP(u,LAMBDA(m,SUM(--(e=m))))))

This is beautiful, I wish I understood how it works, but maybe I can dissect it when I have more time. Is there any way to get this to work in a similar fashion to subtotal where it's only performing this count for a specific date range if this data is in a filtered table? Perhaps a conditional statement that uses the subtotal(MIN) and subtotal(MAX) from the table dates column to get these counts for a specific date range?

 

[Fluff]

Hi & welcome to MrExcel.
Please start a new thread for this question & post some sample data.