Excel & Transpose

Astaroth

I have an archaic way of getting people to enter data into a web form that I cannot change. Each for contains 5 questions and stores the data into a non-relational database which has to be exported to excel before it can be manipulated etc.

The "problem" is that when it is exported to excel it has only 3 columns:

Unique ID
Question Number
Response

By sorting by unique ID I then get a repeating set of responses but effectively all the data I want is in a single column (response) where as what I need is to have 1 row per submitted form (ie unique ID) with the answers to each of the 5 questions under seperate rows.

I know I can copy and paste special transpose each 5 rows in the current single column to achieve this but as there are 5,000+ responses this would take hours to achieve manually.

Does anyone have any idea on how I can do this automatically or at least in under an hour?

Chippy_Minton

Astaroth wrote:

I know I can copy and paste special transpose each 5 rows in the current single column to achieve this but as there are 5,000+ responses this would take hours to achieve manually.

Does anyone have any idea on how I can do this automatically or at least in under an hour?

Record the keystrokes and mouse clicks in a macro (Tools - Macro - Record New Macro). When the macro recorder fires up make sure the Relative Reference icon is 'on'. You can assign the macro to a key and keep pressing the key, or modify the VBA code and put the code in a For Next loop. A bit of experimentation will probably be required to get it right, so practice on a copy of the data.

kevin.philips

Hi,

Are the responses you refer to numerical by any chance...?

If so you have the option of using a pivot table as follows:



Or you could use a macro as suggested by Chippy, either using the macro recorder and having a go yourself or if you get time point the question at the following board:

https://www.mrexcel.com/board2

It has 10's of thousands of users worldwide, all very friendly and I'd bet they'd be able to help you and very quickly too.

Regards

Kevin