We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Excel & Transpose

Astaroth
Posts: 5,444 Forumite
in Techie Stuff
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?
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?
All posts made are simply my own opinions and are neither professional advice nor the opinions of my employers
No Advertising or Links in Signatures by Site Rules - MSE Forum Team 2
No Advertising or Links in Signatures by Site Rules - MSE Forum Team 2
0
Comments
-
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?0 -
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
Kevin0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.4K Banking & Borrowing
- 252.9K Reduce Debt & Boost Income
- 453.3K Spending & Discounts
- 243.4K Work, Benefits & Business
- 598K Mortgages, Homes & Bills
- 176.6K Life & Family
- 256.5K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards