Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
=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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
changing proportion of chart and data table | Charts and Charting in Excel | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Data Table - Does it work with DDE links and stock tickers? | Excel Discussion (Misc queries) | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |