Hi, I'm new to all of this, but I've been having trouble finding any code or formulas out there to help out with this type of data specifically.

I have data that as 872 columns - each representing a question ID (headers in the first row). I then have 1494 rows of data where each represents 1 unique person. In other words, A2 = Person ID and B2-AGN2=the potential answers to the questions.

What I'd like to do is compact this into 3 columns: "Person ID", "Question", "Answer".

"Person ID" will have duplicate values for each question that is answered.
"Question" is the Question Text
"Answer" is each answer to each of the questions.

So in a condensed form, my data looks like this:

ID Q1 Q2 Q3 QAge
3 1 5 8 35
4 1 2 12 64


And I would like it to look like this:

ID Question Answer
3 Q1 1
3 Q2 5
3 Q3 8
3 QAge 64
4 Q1 1

etc.

It would be fantastic if Excel has the functionality to ignore null answers and therefore just not even bother populating Question ID when an Answer is blank (e.g. they didn't report an age, so QAge doesn't show up under the new "Question" field), but I have no idea if that's doable.


I have a lot of datasets like this with a varied number of rows and columns, so it would be helpful if there was a way to adjust whatever formula/macro is out there to work for those. I'm terribly new with macros and so I've been having difficulty adapting them if I need to.


I'm using Excel 2007.


Any help you can offer would be greatly appreciated. Thanks!!!