Pivot table with multiple data entries in one cell.

Hello there,

It's kind of hard to explain so let me just show you a table.

Column1 Column4
Num1 111
Num2 111
Num3 111, 112
Num4 112
Num5 111
Num6 112
Num7 112, 111
Num8 112, 111

Let's say this is my data, I want to create a pivot table that will separate those "111, 112" entries and sort them as if they were both 111 and 112. Giving me a table that would count the occurrences and in which Column1 rows they appear in.

This feels like it should be simple but I can't figure it out, thank you.

Hey Glenn, thank you for responding. I think you are correct in that I made a mistake. I believe I have Excel through O365 (version 2307 is what it says).

This is exactly what I'm looking for. I still have one problem though which was due to me giving a bad example. The values I need sorted like this are text strings like "Staphylococcus epidermidis, Micrococcus leteus". Maybe I just changed the wrong things when adopting your formula, I'll create a file to demonstrate next comment. Thank you once again. (Also just to address some common troubleshooting, my results area is empty, the only values I changed were the ranges (A2:A9, etc))

Hello, sorry for the delayed response, I've tried the formula but it unfortunately doesn't seem to be working. I created a more accurate mock Excel that might help you help me. Thank you very much once again.

It works perfectly, thank you!