Good morning.

We have a bit of a dilemma. Received a large spreadsheet with data from a
survey. However, the information we need to extract for analyzing was
entered into one column as combined data separated by a colon.

Example:

Column A - Name Column B - Programs Used
Johnson Word:Excel:Access
Jones Access
Smith SoftPro:Word:Excel
Wright Excel:Access

We need to be able to show how many people use each program AND a list of
each of those specific people. i.e.:

Access: 3 people
Johnson
Jones
Wright

Word: 2 people
Johnson
Smith

Excel: 3 people
Johnson
Smith
Wright

SoftPro: 1 person
Smith

I know we can separate the data out text to columns by the ":" - however, we
have more than 100 programs as possibilities. And even then, the data is
hard to analyze because they don't separate out into matching columns (i.e. -
Column A Column B Column C Column D
Johnson Word Excel Access
Jones Access
Smith SoftPro Word Excel
Wright Excel Access

Anyone have any quick and dirty ideas?

Thanks.
Elf