Excel - Consolidate data from multiple rows into one row....

Ok, this is what I have.  This is an Excel spreadsheet question.

I have 700 rows of data.  Within each row is a question of a question set.  Also included in each row is the answer to each one of these questions as well as a unique identifier for the question.  Since each question sometimes contains multiple answers, in many cases each question is listed multiple times (i.e. a question with four answers will be listed in the spreadsheet four times – the question and the answer.  

What I need to do is consolidate the data so each question is listed only once.  To do this the corresponding answers will have to be listed in separate cells, in the same row as the question.  Without copying and pasting, what can I do to consolidate each answer into the same row as the question it goes with?  Remember, each question does contain a unique identifier which I hope helps.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

PodExpertConnect With a Mentor Commented:
So, sorry for being a bit late - my time zone is GMT+1 :) Here is the macro you need:


  With Worksheets("Sheet1")
    PrevI = 1
    PrevID = .Cells(PrevI, 3).Value
    ID = .Cells(PrevI + 1, 3).Value
     i = 1
     While ID = PrevID
       .Cells(PrevI, 14 + i).Value = .Cells(PrevI + 1, 14).Value
       .Rows(PrevI + 1).Delete
       ID = .Cells(PrevI + 1, 3).Value
       i = i + 1
     PrevI = PrevI + 1
     PrevID = .Cells(PrevI, 3).Value
     ID = .Cells(PrevI + 1, 3).Value
    Loop Until PrevID = ""
  End With
To use the macro, do this:

Before you start, backup the original XLS file with the questions. One never knows :)

First, sort your table by the QUESTION ID and then REMOVE ANY HEADERS - in the first row, there must be the first question. Now, in main menu of Excel, select Tools -> Macro -> Macros. Then enter the name of your macro, e.g. "MyMacro" and press "Create". Visual Basic editor is now opened, paste the above macro between "Sub MyMacro" and "End Sub" lines. If the name of the sheet with the questions is different from Sheet1, change the 1st line of the macro to appropriate value!
Now, you can either choose Run -> Run Sub/User Form from the VB editor menu, or switch back to Excel and select Tools -> Macro -> Macros ->  MyMacro -> Execute (or Run - don't know, I am using a different language than English in Excel).

The macro will perform everything you needed.

Although it looks simple, it is almost impossible to do it without VB scripting. I can provide you with the script, but I would need the exact format of the data - what columns are there, where are the questions and other information etc. Otherwise I would have to create a very general, configurable script, which would be hard to use for you. I guess you are not familiar with scripting, are you?

The simplest way WITHOUT scripting is using Data->Filter->Automatic filter, and than each time select ONE unique key from the drop-down list. This will fiter the answers related to one question. Now you would have to process it manually.

Maybe there is an easier way, but right now I have no simple idea.

jz1englishAuthor Commented:
No, I am not familar with scripting.   Do you mean you would need the exact name of the question?  Not just the column names?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

jz1englishAuthor Commented:
there are so many question that writing them all out would be impossible.  The column names are the following in this order.  Each name is located in row one for each corresponding column.  The column "Response Text" is what I need to match up with column "text" while still maintaining the other feilds.

Column A: Blank
Column B: Blank
Column c: Question_ID
Column d:Control_ID
Column e:Owner
Column f:Resource_Type
Column g:Mnemonic
Column h:Question_Set
Column i:Question_SubSet
Column j:Text
Column k:Adaptiveness
Column l:Response_ID
Column m:Parent_Response_ID
Column n:Response_Text

Well, so you want to put all responses to the corresponding question's row, i.e. moving the information from columns l, m  and n of the particular responses (except the first one) to the fields o,p,q for the second response, r,s,t for the third one etc. of the original question?
jz1englishAuthor Commented:
yes, that is what i wish to do, except I don't care about moving columns l and m. just n, the "response_text".  thanks...
jz1englishAuthor Commented:
Works great, thanks a lot for the assistance!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.