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

A B C D
2 Johnson Word Excel Access
3 Jones Access Word SoftPro
4 Smith SoftPro Access Excel
5 Wright Excel Word

Enter a "CountIf" at the end of each column for each criteria (row100 maybe
for Access row101 for excel, row 102 for Word...etc
=COUNTIF($B$2:$B$30,"Access") =COUNTIF($C$2:$C$30,"Access")......
=COUNTIF($B$2:$B$30,"Word") =COUNTIF($C$2:$C$30,"Word")......
Sum up the totals for each catagory
then for the naming:


H I J K
1 Access Word SoftPro Excel

2

3

H2

=IF(AND($B2=$H$1),$A2,IF(AND($C2=$H$1),$A2,IF(AND( $D2=$H$1),$A2,IF(AND($E2=$H$1),$A2))))

I2
=IF(AND($B2=$I$1),$A2,IF(AND($C2=$I$1),$A2,IF(AND( $D2=$I$1),$A2,IF(AND($E2=$I$1),$A2))))
"elfmajesty" wrote:

J2
=IF(AND($B2=$J$1),$A2,IF(AND($C2=$J$1),$A2,IF(AND( $D2=$J$1),$A2,IF(AND($E2=$J$1),$A2))))

K2
=IF(AND($B2=$K$1),$A2,IF(AND($C2=$K$1),$A2,IF(AND( $D2=$K$1),$A2,IF(AND($E2=$K$1),$A2))))

drag down
you can conditional format where "FALSE appears (there is no listing for
that item then)

I will be here another 20min, I can send you a sample sheet if you wish



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

Wow! Thanks for your effort on that! It may be a bit too convoluted though
to work efficiently. I had started more with separating the text to columns,
then using an IF(COUNTIF) type formula to get the data. i.e.

=IF(COUNTIF(B2:B100, "program name"), "Yes", "").

The only problem with that was having to change the formula 100 times to
include the new program name!!

Someone else came up with this:
It is a bit quick and dirty, but try this. To start with (using your
example) leave your column B as it is (with the data starting in row 2), then
put the names of each program as headers in row 1 from column C onwards.

Then in cell C2, put the formula

=IF(ISERROR(SEARCH(C$1,$B2)),0,1)

This will return a 1 if the program name appears in the string in B2, and a
0 if it doesn't (so if your column header is "Access" it will return a 1 for
Johnson, Jones and Wright, and a 0 for Smith).

Copy the formula to all the rows and columns: you can then (a) do a sum of
each column to give you the total number of users, and (b) sort by each
column to give you a list of names which you can copy and paste onto a
separate sheet.

This at least gives the information separated out and usable with a simple
one formula copy over to all cells. Only need to input the Program Names as
column headers (which, in essence, is the equivalent of changing the formula
I came up with in the first place 100 times!!)

I'll let you know how it goes.
Thanks for the effort and input.
Cheers,
Elf




"ufo_pilot" wrote:

A B C D
2 Johnson Word Excel Access
3 Jones Access Word SoftPro
4 Smith SoftPro Access Excel
5 Wright Excel Word

Enter a "CountIf" at the end of each column for each criteria (row100 maybe
for Access row101 for excel, row 102 for Word...etc
=COUNTIF($B$2:$B$30,"Access") =COUNTIF($C$2:$C$30,"Access")......
=COUNTIF($B$2:$B$30,"Word") =COUNTIF($C$2:$C$30,"Word")......
Sum up the totals for each catagory
then for the naming:


H I J K
1 Access Word SoftPro Excel

2

3

H2

=IF(AND($B2=$H$1),$A2,IF(AND($C2=$H$1),$A2,IF(AND( $D2=$H$1),$A2,IF(AND($E2=$H$1),$A2))))

I2
=IF(AND($B2=$I$1),$A2,IF(AND($C2=$I$1),$A2,IF(AND( $D2=$I$1),$A2,IF(AND($E2=$I$1),$A2))))
"elfmajesty" wrote:

J2
=IF(AND($B2=$J$1),$A2,IF(AND($C2=$J$1),$A2,IF(AND( $D2=$J$1),$A2,IF(AND($E2=$J$1),$A2))))

K2
=IF(AND($B2=$K$1),$A2,IF(AND($C2=$K$1),$A2,IF(AND( $D2=$K$1),$A2,IF(AND($E2=$K$1),$A2))))

drag down
you can conditional format where "FALSE appears (there is no listing for
that item then)

I will be here another 20min, I can send you a sample sheet if you wish



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