Consolidate Non-numeric data

B

Brian

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.
 
J

Jason Morin

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?
Click to expand...
Y Y
Do you like green? N N
Do you like purple? Y Y
Do you like blue?
Click to expand...
N N
Do you like white?
Click to expand...
Y Y
 
D

Dave Peterson

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.)
 
G

GregR

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

Greg
 
D

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.
 
G

GregR

Dave, thanks again.

Greg
Dave Peterson said:
=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.
Click to expand...
 
Top