Hi all,
I am new here and and in need of some help. This is technically a cross post, you can find the original message in the link below. However, I will keep my query here MUCH briefer.
Essentially, I have a data set generated from a survey-like tool for a multiple choice question. When the data is exported into excel it shows up as a row with all the multiple choice questions as the header, but only the chosen options are filled out under the respective header row and the rest of the MCQ options show up as blank cells.
For instance the question could be 'What is/are your favorite color(s)?' the MCQ options are 'red', 'green', 'yellow', or 'blue'. For three different people answering, the data would show up as:
What is your favorite color?
red green yellow blue
red
yellow
green blue
(this should display as red under red, yellow under yellow, etc.)
Is there a way for me to search a range of cells (A1:A5) such that it finds the text string in that range and then returns to me the value of that text string? In this case for row 1 of the data 'red', then 'yellow', then 'green' AND 'blue'? The trick is that I do not know which of the options will be selected from the multiple choice questions ahead of time so I do not know which items will appear in the row. I can use ISTEXT or ISBLANK to identify the respective cells but do not know how to get it to show the value of the the cell in which it finds text.
I hope that makes sense... The full background can be found in this link along with some other questions I had. But I just found the page on chandoo.org about using vlookup, index, match, and offset to possibly retrieve more than one column/row of data, so I will try to figure that out myself
http://www.excelforum.com/excel-for...nd-then-matching-it-to-a-reference-table.html
Thank you very much for any help. It is much appreciated.
-Nibs
I am new here and and in need of some help. This is technically a cross post, you can find the original message in the link below. However, I will keep my query here MUCH briefer.
Essentially, I have a data set generated from a survey-like tool for a multiple choice question. When the data is exported into excel it shows up as a row with all the multiple choice questions as the header, but only the chosen options are filled out under the respective header row and the rest of the MCQ options show up as blank cells.
For instance the question could be 'What is/are your favorite color(s)?' the MCQ options are 'red', 'green', 'yellow', or 'blue'. For three different people answering, the data would show up as:
What is your favorite color?
red green yellow blue
red
yellow
green blue
(this should display as red under red, yellow under yellow, etc.)
Is there a way for me to search a range of cells (A1:A5) such that it finds the text string in that range and then returns to me the value of that text string? In this case for row 1 of the data 'red', then 'yellow', then 'green' AND 'blue'? The trick is that I do not know which of the options will be selected from the multiple choice questions ahead of time so I do not know which items will appear in the row. I can use ISTEXT or ISBLANK to identify the respective cells but do not know how to get it to show the value of the the cell in which it finds text.
I hope that makes sense... The full background can be found in this link along with some other questions I had. But I just found the page on chandoo.org about using vlookup, index, match, and offset to possibly retrieve more than one column/row of data, so I will try to figure that out myself
http://www.excelforum.com/excel-for...nd-then-matching-it-to-a-reference-table.html
Thank you very much for any help. It is much appreciated.
-Nibs
