Solved

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

Posted on 2004-09-30
7
1,544 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Viewers will learn how to use the Hootsuite Dashboard.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now