Analyzing Multiple Entries In a Single Cell

[SoNoFu]

I apologize for the subject line. I wasn't sure how to title this post. I recently administered a survey for a college research project. The survey results were compiled automatically in an Excel spreadsheet. However, several of the questions had multiple answers which were recorded in one cell separated by commas. I am trying to analyze the results using multiple metrics and I am having trouble getting Excel to count the data in the multiple entry cells as unique values. It will be better if I show you what I am talking about.

Example:

Company Name	Size	Software Used
CO1	S	P1, P2, P3
CO2	M	P1, P3
CO3	M	P2, P3
CO4	L	P1, P3, P?

In this example, I want to analyze which software packages are used by the different size companies. For example, medium companies might prefer one software package while large companies prefer another. I hope that makes sense.

To make matters slightly more complicated, there was an additional option where the person taking the survey could fill in any additional software packages they used. This is represented by "P?". Is it possible to find these unique entries without knowing what they are before hand. The survey results ended around 1000 rows and it would take forever to find all of the unique entries.

If you need some additional information to help me figure this out, please feel free to ask!

[Ron Coderre]

With your sample data in A1:C5

and F1:I1 containing these values: P1 P2 P3 P~? <-Need the tilde in this one so Excel matches the character, not the wildcard
and D2:E5 containing these values: S M L * <- this wildcard will match all sizes

This regular formula, copied across and down through I5, returns the corresponding counts

Please Login or Register  to view this content.

With your data, these are the results:

Please Login or Register  to view this content.

Is that something you can work with?

[SoNoFu]

Thanks Ron! I believe that will work perfectly.

However, I have an additional problem. My above example should have been clearer on the type of data in the "Software Used" column. It would actually look more like this:

Software Used
SketchUp, AutoCAD, MS Excel
SketchUp, Photoshop, MS Word

and on and on.

Now what I need is a formula that will extract unique values separated by ", ". There are spaces between some of the software titles, so I need to pull unique values separated only by the comma and space. If there is no easy way to do this, that is fine. The previous formula you supplied solves a huge part of the problem I am facing.

Thanks for all the help!