Excel: How to analyze cells with multiple values?

Question

i have a table with a column NeedSegment containing cells with values like the followings:

• OEMrs;#Partners;#Balancers;#DoItYourselfers;#Savers
• OEMrs;#Partners;#Balancers
• Savers

A different column is named Portfolio and contains normal single values.

In the end I would like to have a chart which displays per Portfolio how many rows have a specific needsegment e.g. Partners.

I've tried already with consolidated multiple pivot tables (Label contains) or countifs. But it seems to me far too complex...

Any advice or is additional information needed?

Answer 1

I can't see an easy way to deal with this without splitting first the data with the ; semicolon separator.

First of all, split your data:

1. Highlight all of your cells with the data:
   1. Select the topmost cell in the column, e.g. A1
   2. Hold CTRL+SHIFT and then press the down arrow.
2. OK, once we've done that, go to "Data" menu and select "Text to Columns".
3. On the Text to Columns window, select "Delimited" and then click "Next".
4. In the following window, choose both "Semicolon" and "Other" under Delimiters, and type # in the box next to "Other".
5. Check the box labeled "Treat consecutive delimiters as one".
6. Click Finish.

Source

Note: you could also do this with formula (but that would be a pain in the ass, except if you already know how many values there can be surrounding the semicolon)
Note2: you could also do this with VBA if you needed to repeat it several times

Secondly, you can build a Pivot Graph as described in ozgrid

[EDIT] Another solution - with formulas

What you can do if you only want to count the number of NeedSegments per value is to use this formula :

=SUMPRODUCT(IF(FIND("#Partners",A2:A20)>0,1,0))

You can refer to another cell to get the result for each NeedSegment:

=SUMPRODUCT(IF(FIND(B1,$A$2:$A$20)>0,1,0))