Find multiple strings in 1 row, even duplicates

[SP4NU7CH]

I'm struggling with to come up with an working formula. that will count/find the given set of string in a row. I either get no result, or 1 result.

e.g. row

Data A, Data B, Data A, Data C, John, Doe, Data E

the given set of strings what should be counted. ( I have these in a separate column, however these are fixed so it doesn't matter if I use the column or use plain text in the formula (so I have 5 formulas) )

Data A

Data B

Data C

Data D

Data E

So in the given example, I should have 2x A, 1x B, 1x C, 1x E. Rest does'nt matter. 

They are always comma separated, and you don't know how many strings there can be (so that rules out split column based on comma?)

Tried several formula setups. But can't seem to get it working. Any ideas? 

[SjoerdV]

This should be fairly easy in Power Query. As fas as I understand, you have one field with the text-string in it. Split this into columns, based on the comma seperator. Then pivot your data and group by where you count each Data X.

Let me know if it works!

[SP4NU7CH]

Forgot to mention, it's 1 column row, of many columns. Wouldn't your way create a-lot of duplicates? 

[v-piga-msft]

Hi @SP4NU7CH,

Do you have the data like below?

And you want to get the output below?

I'm still a little confused about your scenario, could you please share your data sample and your desired output, so that we can help further investigate on it?

Best Regards,
Cherry

[SjoerdV]

Remove duplicates before splitting then 🙂

Do you have a screenshot or a simplified exampje of your data?