Solved

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

Posted on 2004-09-30
7
1,550 Views
Last Modified: 2009-12-16
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
Comment
Question by:jz1english
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

by:PodExpert
ID: 12192717
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
 

Author Comment

by:jz1english
ID: 12193761
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
 

Author Comment

by:jz1english
ID: 12194184
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 3

Expert Comment

by:PodExpert
ID: 12194321
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
 

Author Comment

by:jz1english
ID: 12194374
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
 
LVL 3

Accepted Solution

by:
PodExpert earned 500 total points
ID: 12198711
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
 

Author Comment

by:jz1english
ID: 12199849
Works great, thanks a lot for the assistance!

John
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If your app took Google’s lash recently, here are the 5 most likely reasons.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
The viewer will learn common shortcuts with easy ways to remember them. The viewer will then learn where to find all of the keyboard shortcuts, how to create/change them, and how to speed up their workflow.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question