Help Transposing Large Amounts of Data

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!!!

I wasn't clear -- I didn't change anything in the code. It came up with that error the first time. I figured it might be something that had to do with my data or something simple I needed to adjust, but maybe not. I've tried deleting some of my columns from the raw data in case there's just too much, but I still get the error. Every now and then it highlights a different line in that same general block of code ('Copy and Transpose Data'), but I haven't had it run completely through yet regardless of how much data I get rid of.

Thanks for replying again. Is there something else I should try or give to you to help?

Hi Leith,

Thanks. I did change my worksheet names to be Sheet 1 and Sheet 2, respectively before I ran it the first time. Is there any chance there is just too much data? Like I said, I have 800+ columns of these various question IDs and then just under 1500 people with responses. Can Excel even handle transposing that to so many rows? It's upwards of over a million....It just seems to crash at that last block of code every time I run it.

I really appreciate your help, but I understand if it's just not feasible.

Thanks, and let me know what you think!

NFT

That code definitely informed me that I was being too ambitious. I got rid of a lot of the data, and I didn't get the message -- but I'm still getting an error in the original code when trying to transpose the data.

Any ideas? Or should we just attribute this to me and my data? I really do appreciate all of the help!!

Thank you Lee and thank you nsb for the posting above.
I am new to this forum and have never posted anything. I am not familiar with macros and I am faced with the task of having to handle the transposition of large amounts of data. My data set looks like this:
Row 1 contains 111 columns (111 different titles) from A to DG
Each row contains payroll information in relation to one unique employee.
There are 3681 employees (3681 rows) excluding the title.

The macro would transpose and rearrange the data in a similiar way as to that from NFT86 i.e.

ID Question Answer (111 differently title colums)
3 Q1 1
3 Q2 5
3 Q3 8
3 QAge 64
4 Q1

I hope the above is clear.
Any help will be greatly appreciated. thank you. Sal