Statistics/Charting Question - Please Help!!
 

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

Statistics/Charting Question - Please Help!!
 

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

Statistics/Charting Question - Please Help!!
 

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