Moving data from multiple cells to a new cell

[brianmcgann]

I have a spreadsheet with multiple columns of data. The columns contain answers to a multiple choice quiz taken by various individuals. The format of the columns is as follows:

Q1A
Q1B
Q1C
Q1D
Q1E

Q2A
Q2B
Q2C
Q2D
Q2E, etc.

In other words, there is a column for each possible multiple choice answer for all 10 questions. If a user selects answer "B" for question 1, the letter "B" appears in the column Q1B, and all other columns for Q1 are blank. So in this example for Q1, there would be 5 different columns which would appear as follows:

Q1A: this cell is blank
Q1B: contains character "B"
Q1C: this cell is blank
Q1D: this cell is blank
Q1E: this cell is blank

I would like to move this data from these various cells to one cell. In other words, I would create a new column for Q1, and I'd like to move all of the various answers to that new column. So in the example above, I would want one new column for Q1 with the character "B" in it. I would then repeat this exercise for all 10 of the answer fields.

Can this be done?

[NBVC]

Is it possible for you to post an actual sample spreadsheet (zipped XL2003 or earlier)? It's not easy (at least for me) to picture your layout and expectations with what you have provided.

[brianmcgann]

Attached is a sample file. I'm dealing with 1,000+ records but I'm only sending over 5. I've stripped out a lot of data that doesn't apply to my original question.

I can (and have been) just copying and pasting the answers into the new column, however it is quite time consuming and if there is a way to do this automatically that would be preferred.

Hope this clears up the question, thanks.

[NBVC]

Hopefully the attached is what you are looking for...

I inserted columns at end of each set of questions and pulled the letter from each row....My parts are in red font.

[brianmcgann]

Absolutely perfect. Thank you!