📨 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

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

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?
    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.
  • Hi,

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

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

    image.gif

    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
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.