Count Similar Values

[excelingtoexcel]

Hi, I have a column that has unstructured data. I would like to know the best way to separate similar values in a way so that i can count them. Ex. the ones in the Orange font are similar to one another. I would like the ability to group all rows containing "Allscripts", and also be able to breakdown if it is Allscripts MA" or "Allscripts - IL". The ones in the green are similar.

Background: it seems like the order in which the selections are made in the source system, is how it ends up displaying in the export of raw data. Each row can have a different # of selections.

I have attached a sample set. Please let me know if there are any questions.

Any assistance would be appreciated!

[Bo_Ry]

Maybe try
Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.

Please Login or Register  to view this content.

[excelingtoexcel]

Thank you for the prompt response! I tried that but got the attached error.

[excelingtoexcel]

Hi @Bo_Ry, i was wondering if you could advise on the error i received after following your instructions.

[Bo_Ry]

I don't see any error on the file from post #2 ,no idea where it went wrong.