Combine 2 Rows Of Data Into One Line While Summing Their Contents?

Jul 1, 2013

Basically, we have 2 tablets tallying data and writing to the same .csv file. That csv file is then copied into a front page of a much larger excel worksheet where the tallied data is sorted and used for a variety of reasons. This works perfectly using index and match when only 1 tablet is being used, however, when both tablets are writing to the same csv at the same time it creates 2 lines instead of 1 line.

For example 1 tablet might look like this:

Lot 45689 4 6 8 9 1 5

2 tablets looks like this:

Lot 45689 2 3 1 8 1 4
Lot 45689 2 3 7 1 0 1

for the tallies, i would imagine a =SUMIF function might suffice, but that doesn't solve my problem of there being 2 lines with the same lot #.

I am only dealing with this issue from the excel side, and have no control over the tablet functionality or the CSV file which further limits me.

Wanted to add that the Lot #s change daily and often so with my understanding of pivot tables this rules them out as a viable option.

View 10 Replies


ADVERTISEMENT

Combine 2 Cells With Line Breaks In Each Of Them And Keep Line Break Formatting?

Aug 5, 2014

I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:

A
B
C

1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street

View 7 Replies View Related

Read Pdf Or Doc File Line By Line And Then Parse Data Into Proper Cells And Rows?

Feb 23, 2013

I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.

View 11 Replies View Related

Combine Contents Of Two Cells Depending On Contents Of Another?

Jan 14, 2013

I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!

I have provided a link to the example file below:

[URL]

View 4 Replies View Related

Combine Duplicate Data From Different Rows?

Jul 25, 2012

I have a table (is populated in a macro already) that has two header rows (1 & 2), inputs a blank row on row 3, then starts data input on row 4. The data can potentially run to row 1000. The columns that are used are B through M. The part name is in column B and the quantity is in column C. The part name can repeat with a different quantity and be put in a different row than a previously entered value. I need to combine those x number of rows based on the match of the part name and sum the quantities then delete the remaining matches. Columns D-M are not unique.

I have attached a sample. The sample is very simple with just two matches but the matches can more than two. The matching rows are highlighted. duplicate rows.xls

View 9 Replies View Related

How To Combine Rows Of Data With Like Information

May 13, 2014

I have 500 rows of data with 6 columns. Columns A,B,C,D,E are text, and column F is values.

I'm looking for a formula where I can say for any of these rows where the data in columns A through E match each other in those rows, combine them into one row of said text, with column E values totaled. I thought I could use SUMIFS for this, but I only know how to define SUMIFS with the criteria being a specific cell/text/value for which it should search on. In my example, the data in the cells can be anything, I just need rows to be "collapsed" to one row for like data.

View 2 Replies View Related

Combine 2 Rows Of Data If Conditions Are Met

Jan 17, 2013

combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.

Example.xlsx

What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).

View 3 Replies View Related

Merger Or Combine Data In Rows

Mar 13, 2007

I am hoping to combine repeated data into single rows. The best way for me to explain is to attach a spreadsheet with an example of how the data is and how I would like it presented. The spreadsheet has comments to highlight this.

View 3 Replies View Related

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

Combine Data In Rows For Certain Fields Only

Aug 8, 2008

I need to combine data from multiple rows to single rows, for certain fields only. The database is large and can described as having an ID column, followed by columns 2 to n that do not need to be combined, followed by columns n+1 to N that do.

I've attached a sample dataset along with the desired outcome. For the purposes of illustration the sample uses n=3 and N=5 but the actual dataset has much larger dimensions.

View 9 Replies View Related

How To Combine The Description In One Line

Jun 17, 2014

Attached is a file containing stock codes and description. The stock code is repeated but i would like to have one line description per stock code.

View 4 Replies View Related

Combine The Code Below In One Line?

Mar 24, 2009

Need to combine the code below in one line?

View 2 Replies View Related

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 View Related

Excel 2010 :: Combine Rows Of Data Into One Row

Mar 7, 2013

My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.

The file was a PDF at first but I have since been able to convert to excel format.

The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.

The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.

The file looks like:

|A---------|B-------|C----------|
1.John-----|Doe-----|123 Main St|
2.New York-|NY------|
3.11100----|SSN----|Balance Owed|

I would like the data from multiple rows to continue on row 1 like this:

|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------|
1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|

I am no programmer in the least and have very little to no experience in pivot tables.

This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.

How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.

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 And Sum Rows Of Data In Excel Dependent On 3 Conditions?

Jan 3, 2014

I have a dataset that, due to the nature of how it is generated, separates certain orders into separate rows. Which orders these are is irrelevant, I just need a quick and easy way to make excel re-combine these rows in to one entry, and sum up the data in one particular column in the process of doing this.

The separate entries of a single order can be identified by the data in 3 columns matching - I have attached an example.

The constituent parts of the same order can be identified when the country, city, and code columns all have matching data as you read down. E.g., where

Country City Code
a 1 12345
a 1 12345
a 1 12345

Where this is the case, I want excel to automatically sum the count of shops for this order and then delete all but one of these rows, with the remaining row now containing this summed figure. Failing this, a new worksheet being generated/a new column giving me this data would be good.

View 6 Replies View Related

Combine Area And Line Charts

Jan 14, 2010

I have made two line charts from a selection of data and added "shapes" to fill certain areas under the lines. Is there a way of doing this within the chart rather than adding a shape over the top?

View 3 Replies View Related

Combine Cell Contents With Named Range?

Aug 8, 2013

I have a cell with a drop-down menu where the user can select "Plant A" or "Plant B".

I have a cell below that with a sales number, which comes from a different tab where the sales number is input and has a named range.

For example, the input tab has a named range called "Plant A Sales" and a value of 1234, and a named range called "Plant B Sales" with a value of 5678.

I want to put a formula in a cell that will look at the cell where the drop-down menu is and will populate that cell with the correct data for the selected plant.

So if the user selects "Plant A" from the drop-down menu, the cell will show "1234". If they then select "Plant B" from the drop-down menu, the cell will show "5678".

View 1 Replies View Related

Delete All Rows/Data Above This Line

Nov 20, 2008

Let's say I have the following file which is imported via a macro which comes from a txt file. [Apologies it's 7mb zip file] http://www.fileden.com/files/2007/8/...mpNotFixed.zip

The way the txt file is that two rows make up one row, but SAP spits the data out into two rows. Now there are specific things that I need from these tabs. The way i can get this data is to perform sort by descending on the tabs. When you sort by a descending you get the data that i need [at the second half of the worksheet]:

Basically what I want to know when i create a macro, how can i get it do delete all the rows above a specific row. In the attached file, go to DataImport2.
I would like a macro to find the word "English" in column C and then delete all the row's above it. I cannot record this because it copy's the specific cells, which i do not need. DataImport3 is how the data comes out, All i do is sort by descending ... and i need help as stated above to delete all unnecessary data.

View 3 Replies View Related

Row Combine Till Address Line Not Complete?

Jun 25, 2013

I want to combine row while address row not complete. I attach example xls file in this post.

View 3 Replies View Related

Insert Rows Below Every Line That Has Data In Column A?

May 5, 2014

i have numbers in column A1:A556, i want to insert a row below every column that has a number so that they can have double spacing.

View 1 Replies View Related

Data From Rows And Column To One Single Line

Nov 12, 2009

I have to worksheets: Sheet1, Sheet2. In sheet 1, I have some data like this (in first col is 1, second col is a, third col is c etc. ):

1 a b c d
2 e f g h
.
.
.
.

I need in sheet 2 in range A1(only in this range one big text), something like this: a,c,e,g ........ ( row1-col2,row1-col4,row1-col2,row1-col4 ......) ( separated by ","). The number of rows from sheet 1 is variable. The dataf a,b,c,d,e,f,g,h ... is some random text.

View 4 Replies View Related

Many CSV Files Containing Common Names - Combine Across Sheets To One Line

Jun 27, 2013

I work for schools use data to guide teachers practices in the classroom. My main function is to mine down through data for kids that teachers focus on specific skills with specific children.

I start with a big conference each summer. By hand I combine, by student name, the data collected over the past school year. We then tear down to student levels setting goals based on statistical analysis. I need to combine across all of these csv files in a way that appends the data from each into one line per kid. I thought that 'vlookup' or 'index, match' might do it. I don't know.

View 14 Replies View Related

Tagging Rows Of Data Based On Cell Contents

Apr 15, 2014

I'm am trying to set up a household finances dashboard in excel - What i'd like to do is categorise & tag HH expenditure based on part of the description within a bank statement, the desired result being:

account tagging.jpg

My ideal solution would be some kind of lookup with a table containing the keywords & corresponding tags to match part of the description to, as this will allow easy management of the tags, situations where I have multiple keywords for the same tage (supermakets for example), and so forth. Is this possible?

Currently I am using a very large nested forumula which i've cobbled together based on searching for ideas. It works, but managing new tags is difficult, part of the formula i'm using is:

=IF(ISNUMBER(FIND("00111888",'JOBSFPCUR-20140414'!C4)),"Pete transfer",(IF(ISNUMBER(FIND("LAURA",'JOBSFPCUR-20140414'!C4)),"Laura Pocket Money",(IF(ISNUMBER(FIND("TESCO",'JOBSFPCUR-20140414'!C4)),"Supermarket")))))

View 2 Replies View Related

Add A Single Cells Contents To The LINE That Is Being Copied

Jun 10, 2006

I have some code (authored by Dangler) that i want to pull not just one range but also the contents of one specific cell and then copy the range and cell to a line on another spreadsheet.

Question:

f .Cells(i, 8).Value > 0 Then

.Range(Cells(i, 1), Cells(i, 200)).Copy

Sheets("all").Range("a65536").End(xlUp).Offset(1).PasteSpecial (xlValues)

End If

I want to add a single cells contents to the LINE that is being copied .... something like this...

f .Cells(i, 8).Value > 0 Then

.Range(Cells(i, 1), Cells(i, 200)) AND A1.Copy

Sheets("all").Range("a65536").End(xlUp).Offset(1).PasteSpecial (xlValues)


End If

the output would be like this: CELL CONTENTS, RANGE CONTENTS
or
K21, 40012122 400 450 450 50 001 BAGS

View 9 Replies View Related

Email Spreadsheet With Subject Line Filled Out From Cell Contents

Dec 1, 2011

Is there anyway this can be done using a button in the spreadsheet?

For Example.

I send numerous almost identical forms to a colleague and to eliminate subject line typo's (Reference numbers) i would like the Subject line to match a cell in the document itself.

I have experience in working with Excel, but none on using code/macros

View 2 Replies View Related

New Line With VBA On Rows And Merging New Line

Mar 14, 2013

The following code adds a new row at a given point (Based on a fomula in a column returning "1")

The code works well BUT I want to merge this new row from column A to coulmn M

Im not sure how to do this, (I dont want to merge the entire row)

Example Code:

Sub ExtLne()
Dim Lastrow As Long
Dim I As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
I = 1
While I

View 2 Replies View Related

Excel 2010 :: Cell Summing Through Referencing Neighbor Cell Contents?

Feb 12, 2014

we have a Excel 2010 worksheet containing multiple tables.

Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10

Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17

On a separate worksheet we want to calculate with the first worksheet's values.

For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange

How do we do such a calculation/formula?

View 4 Replies View Related

Compiling Data From Multiple Workbook Into A Line By Line Master Schedule

Mar 30, 2013

I have about 180 workbooks which I need to compile into a Master Schedule.

All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?

Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?

View 1 Replies View Related

Read Range Of Data Line By Line Which Is Increasing Dynamically

Aug 20, 2014

I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.

vbaStk.JPG

What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.

I'm new to VBA and I'm not so sure what I'm doing with vba codes

View 1 Replies View Related







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