Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-09-30
7
1,554 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

792 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