Combine Data From Multiple Rows Into One Single Row?

Jun 8, 2013

I have a set of data with 5 columns. The first column is ID. Now I have the same ID recorded several times with different data (in the other 4 columns) against the ID. I want to concatenate the data with the same ID into the same row. The data has already been sorted by ID. If it is the first time this ID appears, record the whole 5 columns. If the ID appears again, then record only the 3rd, 4th and 5th column. When I run my code, I got error 'subscript out of range (Error 9)' . It seems that the array I use has only 5 columns. But I don't know how to modify this.

VB:
Sub Patient_Detail()
Dim n As Integer 'index of rows to record to
Dim i As Integer 'index of column to record from

[Code].....

View 7 Replies


ADVERTISEMENT

Combine Data From Multiple Rows

Jun 19, 2007

so basically, here's an example of what I have. The spaces in between represent different cells:

Name Corporation Type Group
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 2
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 3

What I want to have is instead of having 3 rows in this instance, I want to have a macro that looks at rows and if it finds multiple rows with the exact same name, corporation, and type, it merges the rows to look like this:

Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1, Council 2, Council 3

In some instances, I have 3 rows that might match, some its 2, some its 10, it varies.

View 2 Replies View Related

How To Combine Multiple Rows Or Related Data Into One Row

Feb 22, 2012

I have a table with multiple rows for some employees and single rows for others. Each employee has a unique identifier.

Employee IDT1T2T3T4123Net salesmarket assetsmarginsspeed to market123Net salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time delivery180unit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter

Is there a way in which I can restructure the data so that the multiple rows for any single employee are added into subsequent columns in one single row? e.g.

Employee IDT1T2T3T4T1bT2bT3bT4b123Net salesmarket assetsmarginsspeed to marketNet salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time deliveryunit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter

The table has approximately 15000 rows & c. 30 columns in the original format. I can use basic excel, but I've never dipped into macros or anything very complicated...

View 1 Replies View Related

Combine Rows Into Single Cells By Condition

Mar 6, 2008

I'm importing some data from an HTML table into excel, it all formats pretty well except that some of the data appears on multiple rows.

A B C
1 01.02.06 John
2 Bob
3 Sam
4 02.03.06 Jim


What I need:

a b c
1 01.02.06 John, Bob, Sam 3
2 02.03.06 Jim 1

What I need to do is get all the items in the colum B which are listed under the same item in column a, in the first case John Bob and Sam and put them all into the same cell. I then want to delete the empty rows. It's important that this is done by testing to see if column A is blank, rather than using the date.

then in column C I want to count the items in column B,
ideally then repeat the script for the whole sheet.

Not a clue of the type of syntax I should be using though!
In fact there's probably a better way of importing the data from the HTML so it doesn't even need to be done this way! of course I'm oblivious to it. Auto Merged Post Until 24 Hrs Passes;Thinking maybe I didn't make myself properly clear....

If a cell in column a is empty then it means the item in column b needs adding to the end of the item in the row above in column b.

I then want to count the items in column b.

View 9 Replies View Related

How To Combine Multiple Trendlines In Single Chart

Feb 15, 2014

I am doing financial ratio analysis of a company for five years and I need to show the ratios in a trendline. I have generated seperate line charts for each ratio, but I want to combine the charts and have multiple trendlines for multiple data series in a chart.

View 3 Replies View Related

Combine Multiple Workbooks Into One Single Workbook

Feb 21, 2010

I am trying to combine ~300 workbooks into one single workbook. All 300 workbooks have the exact same header. I tried using the code from thread http://www.excelforum.com/showthread.php?p=696435 but nothing is being copied over. The only difference between my example and the other is I only need to take data from the first sheet in each data workbook. All the workbooks are located in following directory .....

View 9 Replies View Related

Moving Data From Multiple Rows To A Single One

Jun 29, 2014

Any macro capable of moving data from multiple row to a single one i have attach a sample file before and after ...

View 4 Replies View Related

Transpose Multiple Rows Of Data To Single Row?

Jul 24, 2014

I have a set of data that I need to change the "layout" of. I've had similar situations before, but this one is just killing me. Basically, my data is for item pricing. It is represented as

Item, QTY, Price
A,1,1.25
A,10,1.1
A,25,1
A,100,0.9
B,1,1.25
B,10,1.1
B,25,1
B,100,0.9
C,1,1.25
C,10,1.1
C,25,1
C,100,0.9
Item D,Qty1,P1
Item D,Q2,P2
Item D,Q3,P3
Item D,Q4,P4

However, I need it in the following format:

A,1,10,25,100,1.25,1.1,1,0.9
B,1,10,25,100,1.25,1.1,1,0.9
C,1,10,25,100,1.25,1.1,1,0.9
Item DQ1Q2Q3Q4P1P2P3P4

As a note: there is a maximum of 4 Price/QTY breaks, so the script can be hard coded for that. When I tried this, I had it looking at the Item column, finding out how many breaks there are for a specific item and then doing a loop to extract the qty and price to a single row in the format shown above. It worked for the first 2 items, but then the loop got throw off. I will see if I can reproduce the code for that.

View 4 Replies View Related

Consolidate Multiple Rows Of Data Into Single Row

Jul 28, 2006

I imagine my goal could be achieved via some scripting code, but, alas, I don't do vbs. Here's the situation:
I have a spreadsheet generated by another office which lists individuals' names and information about participation in various programs. Each line lists name, address, program title and amount. The next line may be the same name, address with a different program and amount. Each person may have 4-5 entries. I want to make single entries for each individual, with colums showing the programs and amounts.
Current:
ID Name Address Program Amount
1 Bob home A 25
1 Bob home B 37
2 Dave home A 22
2 Dave home B 10
2 Dave home C 21

Need:
ID Name Address Program A Program B Program C
1 Bob home 25 37 0
2 Dave home 22 10 21

Hopefully this makes it a bit more clear. The only way I know how to accomplish this is the "old fashioned" long hand approach of cut & paste. There are several thousand individuals, with (currently) tens of thousands of rows.

View 9 Replies View Related

Organize Data From Multiple Rows Into Single Row

Jun 12, 2007

I would like to automate, due to how long it would take to manually organize the data myself. I have all of the raw data needed organized into 3 columns. The problem is that some of the related data is divided up into multiple rows, based on ID number. What I would like to do is take the related CPT codes and respective descriptions, from the same ID number, and place them on the same row. I am going to include some pics and even a small chunk of the source data. Here is a pic of the raw data:

And here is the first 3 already done:

The number of CPT codes per ID number range from 2 - 5 I believe, maybe 6. Not sure if this has any bearing on how it is done.

View 4 Replies View Related

Data From Multiple Rows Into Single Column

Oct 19, 2007

I am trying to take data from multiple rows and columns (matrix) and reformat it all to fit in a single column.

It would be great if I could select the cells I want to reformat into the single column as the number of rows and columns containing the original data is not always the same (ie 20x20, 21x35, 56x200) etc...

View 9 Replies View Related

Formula To Combine Multiple Formulas Into Single Cell?

Mar 12, 2014

I'm looking for a way for Excel to combine formulas together into a more compact form for me.

Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.

Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?

Here is the one-cell version of a formula I work with.

[Code] .......

That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.

Attached File : Excel Formula Combination Question.xlsx‎

View 7 Replies View Related

Combine Multiple Files Into One Workbook (single Worksheet)

Apr 12, 2007

I've pieced together code from a number of solutions on the forum and figured out how to find the files I need to process, but can't seem to figure out the code to actually combine/append each worksheet into a single one. I've searched the "sea of options" on this forum and managed to do one thing well. Pardon all my notes embedded in the code below, but if I don't do it that way, I'll only confuse myself further (which is easily accomplished) ... or lose my notes!

Im going to run this code from an add-in menu, which is already created and working (yeaah!) so (I think) I need to have the code either create a file named "Master (Combined).xls" in the target folder ... or ... have a file already created in MYFOLDER and have this code delete all but the header row when the file is opened(???) The "Master (Combined).xls" workbook needs to consist of one worksheet named "Master". I want it to open the current workbook (.foundfiles(i)), select all the data, and append it to the master workbook on the "Master" worksheet (sheet 1). The Master Worksheet in the Master Workbook needs to start out as a blank sheet ... except for the header row. I can add code to put the headers in at the start, if necessary

Sub g_CombineMultWB_AllXLSFiles()
' This Will combine all XLS files located in the
' S:DMSMSPOMSMaster POMS NIIN DataMaster EMALL Data FilesEMALL Excel Folder
' into a single worksheet in a newly created (or previously existing) workbook
'
' LOCATION OF FILES (ACTUAL):
' S:DMSMSPOMSMaster POMS NIIN DataMaster EMALL Data FilesEMALL Excel Folder

Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' Application.CutCopyMode = False ' DO I HAVE THIS HERE FOR A REASON???

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Const MYFOLDER = "S:DMSMSPOMSMaster POMS NIIN DataMaster EMALL Data FilesEMALL Excel Folder"
With Application.FileSearch
.NewSearch
' Change path To suit.............................

View 9 Replies View Related

Retrieving Multiple Rows Of Data From One Single Item?

Jan 3, 2014

retrieve the multiple rows of data from one specific item to another worksheet inside same workbook. below attachment is my sample workbook.

inside the workbook, eg.. please have a look on Sheet 3 (the record of the item and its description) will store on Sheet 2. From sheet 1, when user choose the item from drop down list, and click the button will direct to the retrieved result in sheet 2. my question is i cant retrieve the multiple rows of data exactly in the sheet 2. it only remains one item for one row of description.

and below attached picture is what i wish to achieve =)Capture.PNG

View 4 Replies View Related

Copy Multiple Rows Data To Single Column

Oct 29, 2008

transposing and sorting data into multiple columns.

Column 1 for example will have the Parent's Name. Column 2 will have the Children.

If Mark has 3 children, X Y and Z

Jim has two children, A and B

then I want Column 1 to display Mark and Jims Name and column two should display all the children

Here is how I want the data:

Column1 Column 2

Mark X
Mark Y
Mark Z
Jim A
Jim B

Please see attachment.

View 3 Replies View Related

Single Column With Multiple Rows Of Data - Compare Names

Apr 12, 2012

I have a single column with multiple rows of data like this: Afirst last, Bfirst last, Cfirst last (all in one cell) etc.

I want to type some names in one cell in the same format (Afirst last, Cfirst last, Bfirst last) but not order and then check if any of those names exist in the first column and count the results of matches.

So in this example I am looking to count Bfirst last and/or Cfirst last in every cell of column A. I need to use a formula.

View 2 Replies View Related

Summarize Data From Columns In Multiple Sheets To A Single Datasheet Of Rows

Jun 19, 2014

I need a macro that will take hours by day (columns) by service (rows) per client (sheet) and summarize the data into one database of rows containing client, service, date, and hours. The "Summary of Charges" should only include service hours > zero. I am attaching a sample file. I have little to no experience with vba so I don't even know where to begin. I can copy code.

View 4 Replies View Related

Combine Data Into Single String

Jan 10, 2007

I have a sheet that I need to combine data from three cells into one and then get rid of original data.

Data to be combined:
A1=650
B1=1234567
C1=1998
D1=Desired Output

Desired Output:
A1=
B1=
C1=
D1=650-1234567-1XXX

View 4 Replies View Related

Combine Data From Different Workbooks To A Single Workbook

Oct 3, 2008

how can you combine all data from different workbooks in to one workbook. i have 544 workbooks to combine. here is a sample screenshot of the table i need to combine.

it consists of 9 columns. rows are not consistent in number and sheets are named with numbers starting from 001 to 999 ....

View 9 Replies View Related

Excel 2007 :: How To Combine Data Elements Into Single Data Object

Sep 23, 2011

I am building a rather good size userform. It is getting really lengthy code-wise because I want certain things to be enabled and disabled on the form as they click on certain options.

I know I can hard code the lines but so I am trying to come up with some coding elements where I will not have to add 20+ lines of code for everytime I want to add a new feature.

Here is one of the functions that I have, There is going to be several of these:

Private Sub chkMore2_Click()
If chkMore2 = True Then
cboProtocol2.BackStyle = fmBackStyleOpaque
cboApplication2.BackStyle = fmBackStyleOpaque
txtLowPort2.BackStyle = fmBackStyleOpaque
[Code] .......

So If I click chkMore2 ... all of the 2nd elements will be editable. If I click chkMore3 ... then all of the 3rd elements will be editable, etc. etc.

So I tried to play with this code example:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Call Changeit(1, "yes")
Else
Call Changeit(1, "no")

[Code] ........

Excel 2007 does not like the .concatenate element.

View 1 Replies View Related

Coordinate System - Combine All Data In Single Graphic

Jan 3, 2012

I want to combine all data in a single graphic. It must be like a coordinate system.

The file is in that address: [URL] .......

View 1 Replies View Related

Combine Lab Daily Assay Data Into Single Easily Manageable File

Feb 13, 2014

How to Collate and combine lab daily assay data into a single easily manageable file.

Currently i have a spreadsheet for every days assay results. Now this should be combined into monthly and yearly data.

View 1 Replies View Related

Combine Multiple Rows Into One Row

Nov 4, 2006

I have a 8500 row sheet with 40 series of data given for each "data set". This data is situated in four columns and ten rows, so that rows 1-10 contain one data set, rows 11-20 contain another data set, etc...

Does anyone have a simple VBE script that can grab this data and format it so that my data sets are arranged as 850 rows and 40 columns instead of the native 8500 rows and 4 columns?

View 7 Replies View Related

Excel 2007 :: Combine Multiple Rows Into One

Jan 10, 2012

I have a list of about 4,000 contacts each with about 2 or 3 products with us. Each product creates a new contact row.

I would like to merge each row with the same contact into one row.

I have attached an example : Example test.xlsx‎

View 14 Replies View Related

Combine Multiple Rows Into One Comma Separated Column

Mar 4, 2013

I have a list of companies and employees, if a company has more than 1 employee listed, I need to combine all instances of the employees into 1 row per company separated by a comma.

OLD FILE: Sample.xlsx
NEW FILE: Sample.xlsx

View 9 Replies View Related

Using Lookup Function To Combine Text From Multiple Rows

Oct 4, 2012

At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..

Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"

On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.

Sheet 2

="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss

View 6 Replies View Related

Combine Multiple Rows To One Row With Blanks In Some Reference Columns

Jun 4, 2014

combining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that met my needs. I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me.

Desired output:
Item
Quantity

[Code]....

View 9 Replies View Related

Multiple Rows To A Single Row

Nov 28, 2009

I have a long spreadsheet, that has a format something like this: ...

View 6 Replies View Related

Single Row Into Multiple Rows

Dec 24, 2007

Is there a way convert single cell with wrap text into multiple cells with the number of resulting rows based on the number of lines in the original cell. Due to the restrictions of my overall printable "document", I must use multiple cells of the same height. Any change of row height would throw off the desired final result.

As an example, when I start with my original cell in text wrap and set column width I get something like the following in cell F2:

ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME
MATERIAL)

This looks great, but it is all in one cell, and the four lines of wrapped text have obviously increased the row height which is bad.

I have tried using the LEFT and MID functions to force the text into different rows based on character length, but this is sort of messy since I am never sure what the input will be resulting in something like the following (this time in cells F2:F5) with spaces at the beginning of a line or breaks in the middle of words:

ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME MATE
RIAL)

My ultimate goal would be to somehow format the original input data text so that regardless of the input, the text would be restricted to a certain column width, sending the remaining text to the next consecutive rows down, and maintain the integrity of the original. I can have as many intermediate cells, formatting as I go to get to this point.

Of course, I could manually enter the text in the different rows every time, but it would be nice to have a succession of formatted cells so that the desired end result would be automatic. Hopefully, the answer does not involve too much in the way of macro or any VBA code since I am a pitiful novice, and we are sort of under pressure to get this project under way.

View 9 Replies View Related

Sum Multiple Or Single Rows

Feb 9, 2007

I have a cash receipt worksheet and want to sum up the days cash. My problem is some days there are one receipt and some there are five. I have to keep hitting the sum button to make it include the proper range.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved