Consolidate Non-numeric data

[Guest]

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.

 

[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?

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

 

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

 

[GregR]

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

Greg

 

[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]

Dave, thanks again.

Greg

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