Consolidate Non-numeric data
 

I have a set of data from a large survey that requires multiple people to
fill out for it to be complete. When I export the data to Excel, it looks
like this:


Reply 1 Reply 2 Reply 3
Do you use a car? Y
Do you like red? Y
Do you eat fruit? Y
Do you like green? N
Do you like purple? Y
Do you like blue? N
Do you like white? Y


I would like to produce the following output (effectively placing a copy of
all the replies in one column for easier reference so I don't have to look
for the columns with the answers)


Answer Reply 1 Reply 2 Reply 3

Do you use a car? Y Y
Do you like red? Y Y
Do you eat fruit? Y Y
Do you like green? N N
Do you like purple? Y Y
Do you like blue? N N
Do you like white? Y Y


Does anyone have any thoughts, suggestions, or know how to do this? Thank
you for your help.

One way:

=IF(COUNTA(C1:E1)1,"Multiple Responses",INDEX(C1:E1,MATCH
("*",C1:E1,0)))

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a set of data from a large survey that requires
multiple people to
fill out for it to be complete. When I export the data
to Excel, it looks
like this:


Reply 1 Reply 2
Reply 3
Do you use a car? Y
Do you like red? Y
Do you eat fruit? Y
Do you like green? N
Do you like purple? Y
Do you like blue? N
Do you like white? Y


I would like to produce the following output
(effectively placing a copy of
all the replies in one column for easier reference so I
don't have to look
for the columns with the answers)


Answer Reply 1
Reply 2 Reply 3

Do you use a car? Y Y
Do you like red? Y Y
Do you eat fruit?
Y Y
Do you like green? N
N
Do you like purple? Y
Y
Do you like blue?
N N
Do you like white?
Y Y


Does anyone have any thoughts, suggestions, or know how
to do this? Thank
you for your help.
.

Just another way (since Jason already checked for multiple responses):

=IF(COUNTA(C1:E1)1,"Multiple Responses",c1&d1&e1)

(Jason's formula gets a lot nicer when the number of responses gets larger.)


Jason Morin wrote:

One way:

=IF(COUNTA(C1:E1)1,"Multiple Responses",INDEX(C1:E1,MATCH
("*",C1:E1,0)))

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a set of data from a large survey that requires
multiple people to
fill out for it to be complete. When I export the data
to Excel, it looks
like this:


Reply 1 Reply 2
Reply 3
Do you use a car? Y
Do you like red? Y
Do you eat fruit? Y
Do you like green? N
Do you like purple? Y
Do you like blue? N
Do you like white? Y


I would like to produce the following output
(effectively placing a copy of
all the replies in one column for easier reference so I
don't have to look
for the columns with the answers)


Answer Reply 1
Reply 2 Reply 3

Do you use a car? Y Y
Do you like red? Y Y
Do you eat fruit?
Y Y
Do you like green? N
N
Do you like purple? Y
Y
Do you like blue?
N N
Do you like white?
Y Y


Does anyone have any thoughts, suggestions, or know how
to do this? Thank
you for your help.
.


--

Dave Peterson

Jason or Dave, how is the formula adjusted if the responses are numbers. TIA

Greg
"Dave Peterson" wrote in message
...
Just another way (since Jason already checked for multiple responses):

=IF(COUNTA(C1:E1)1,"Multiple Responses",c1&d1&e1)

(Jason's formula gets a lot nicer when the number of responses gets
larger.)


Jason Morin wrote:

One way:

=IF(COUNTA(C1:E1)1,"Multiple Responses",INDEX(C1:E1,MATCH
("*",C1:E1,0)))

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a set of data from a large survey that requires
multiple people to
fill out for it to be complete. When I export the data
to Excel, it looks
like this:


Reply 1 Reply 2
Reply 3
Do you use a car? Y
Do you like red? Y
Do you eat fruit? Y
Do you like green? N
Do you like purple? Y
Do you like blue? N
Do you like white? Y


I would like to produce the following output
(effectively placing a copy of
all the replies in one column for easier reference so I
don't have to look
for the columns with the answers)


Answer Reply 1
Reply 2 Reply 3

Do you use a car? Y Y
Do you like red? Y Y
Do you eat fruit?
Y Y
Do you like green? N
N
Do you like purple? Y
Y
Do you like blue?
N N
Do you like white?
Y Y


Does anyone have any thoughts, suggestions, or know how
to do this? Thank
you for your help.
.


--

Dave Peterson

=IF(COUNT(C1:E1)1,"Multiple Responses",sum(c1:e1))
(if 0 is ok to show if all are empty)

=IF(COUNT(C1:E1)1,"Multiple Responses",IF(COUNT(C1:E1)=0,"",SUM(C1:E1)))

if you want it to look empty.

GregR wrote:

Jason or Dave, how is the formula adjusted if the responses are numbers. TIA

Greg
"Dave Peterson" wrote in message
...
Just another way (since Jason already checked for multiple responses):

=IF(COUNTA(C1:E1)1,"Multiple Responses",c1&d1&e1)

(Jason's formula gets a lot nicer when the number of responses gets
larger.)


Jason Morin wrote:

One way:

=IF(COUNTA(C1:E1)1,"Multiple Responses",INDEX(C1:E1,MATCH
("*",C1:E1,0)))

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a set of data from a large survey that requires
multiple people to
fill out for it to be complete. When I export the data
to Excel, it looks
like this:


Reply 1 Reply 2
Reply 3
Do you use a car? Y
Do you like red? Y
Do you eat fruit? Y
Do you like green? N
Do you like purple? Y
Do you like blue? N
Do you like white? Y


I would like to produce the following output
(effectively placing a copy of
all the replies in one column for easier reference so I
don't have to look
for the columns with the answers)


Answer Reply 1
Reply 2 Reply 3

Do you use a car? Y Y
Do you like red? Y Y
Do you eat fruit?
Y Y
Do you like green? N
N
Do you like purple? Y
Y
Do you like blue?
N N
Do you like white?
Y Y


Does anyone have any thoughts, suggestions, or know how
to do this? Thank
you for your help.
.


--

Dave Peterson

--

Dave Peterson

Dave, thanks again.

Greg
"Dave Peterson" wrote in message
...
=IF(COUNT(C1:E1)1,"Multiple Responses",sum(c1:e1))
(if 0 is ok to show if all are empty)

=IF(COUNT(C1:E1)1,"Multiple Responses",IF(COUNT(C1:E1)=0,"",SUM(C1:E1)))

if you want it to look empty.

GregR wrote:

Jason or Dave, how is the formula adjusted if the responses are numbers.
TIA

Greg
"Dave Peterson" wrote in message
...
Just another way (since Jason already checked for multiple responses):

=IF(COUNTA(C1:E1)1,"Multiple Responses",c1&d1&e1)

(Jason's formula gets a lot nicer when the number of responses gets
larger.)


Jason Morin wrote:

One way:

=IF(COUNTA(C1:E1)1,"Multiple Responses",INDEX(C1:E1,MATCH
("*",C1:E1,0)))

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a set of data from a large survey that requires
multiple people to
fill out for it to be complete. When I export the data
to Excel, it looks
like this:


Reply 1 Reply 2
Reply 3
Do you use a car? Y
Do you like red? Y
Do you eat fruit? Y
Do you like green? N
Do you like purple? Y
Do you like blue? N
Do you like white? Y


I would like to produce the following output
(effectively placing a copy of
all the replies in one column for easier reference so I
don't have to look
for the columns with the answers)


Answer Reply 1
Reply 2 Reply 3

Do you use a car? Y Y
Do you like red? Y Y
Do you eat fruit?
Y Y
Do you like green? N
N
Do you like purple? Y
Y
Do you like blue?
N N
Do you like white?
Y Y


Does anyone have any thoughts, suggestions, or know how
to do this? Thank
you for your help.
.


--

Dave Peterson

--

Dave Peterson