Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1581
  • Last Modified:

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.

Thanks…
0
jz1english
Asked:
jz1english
  • 4
  • 3
1 Solution
 
PodExpertCommented:
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.

Regards.
P.
0
 
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?
0
 
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PodExpertCommented:
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?
0
 
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...
0
 
PodExpertCommented:
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
   
    Do
     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
     Wend
       
     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.

Regards.
P.
0
 
jz1englishAuthor Commented:
Works great, thanks a lot for the assistance!

John
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now