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


ADVERTISEMENT

If Multiple Conditions Exist Then Combine Text Fields?

Apr 1, 2014

I am trying to build a list of account numbers based on conditions in a spreadsheet. Every account number will look like: XXXXXX-XXX-XXX, depending on if there is a 'Y' in both fields...see attached. I am desperate to get this done and have hit a complete road block.

View 2 Replies View Related

Convert Column Data Fields To Row Data Fields In Pivot Table

Feb 8, 2014

CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83

[Code]....

left side pivot created in vb 6.0 & right side pivot table created manually in excel.

i want to generated pivot table using vb 6.0 same as right side pivot.

Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),

[Code]....

View 2 Replies View Related

Excel Macro - Deriving Fields Based On Multiple Rows Of Data

Dec 4, 2012

- The macro should then try to find this data on the worksheet "Sheet2" in the same workbook. This data on Sheet2 is essentially a table with four columns "Number" (Col A), "Country" (Col B), "Consol" (Col C) and "Bypass" (Col D) but can have around 70k rows

- It should look for Number first, and if its not found, the macro should insert the message "Update OK" in cell A8 on Sheet1

- If it finds at least 1 row for the Number but the Country is the same for all the rows and it matches the value input for Country on Sheet1, then it insert the message "Multiple Records - Update OK" in cell A8 on Sheet1

- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "Y" but the Bypass field input on Sheet1 has "N", then put "Multiple Records - Update OK" in cell A8 on Sheet1

- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "N" but the Bypass field input on Sheet1 has "Y", then put "Multiple Records - Bypass - Do Not Update" in cell A8 on Sheet1

- If it finds at least 1 row for the Number but the Country is the same for all the rows that have a Bypass value of "N" and it does match the value input for Country on Sheet1 then put "Multiple Records - No Need to Update" in cell A8 on Sheet1

- If it finds at least 1 row for the Number but the Country is different for all the rows that have a Bypass value of "N", then put "Multiple Records - Refer" in cell A8 on Sheet1

[URL] ....

TestBook.xlsm

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

Address Fields From Rows To Columns

Aug 8, 2007

I need to Align some address fields that I receive from an import. I have several lines of data that are represented in the attachment in Sheet 1. I'm just looking to clean it up a bit by having the address be listed in one column. I'm hoping to acheive the results that appear on the DesiredResult sheet. I could have sworn I saw a similiar post several weeks ago while perusing Ozgrid, but now that I'm in actual need, I can't seem to find it when searching.

View 3 Replies View Related

Match Multiple Fields In A Row Over Many Rows

Mar 9, 2008

I have 4 colums of customer input data, Length, Width, Height and Quantity. There are over 20 rows to accomodate multiple combinations. I have several other hidden pricing pages with all possible combinations including materials, pricing etc. Assuming I am on the frst pricing page and first combination I need a formula that will match the first 3 inputs per row and if true give me the 4rth input for the matching row.

Example:
Customer input-
row 1 = 8 - 12 - 4 - 15
row 2 = 4 - 4 - 2 - 25
etc.

Order Page-
Combination 1 = 4x4x2 quantity___ (input row 2 matches 4-4-2 give quantity 25)
Combination 2 = 4x8x2 quantity___ (no input row matches 4-8-2 give quantity 0)

Combination 36 = 8x12x4 quantity___ (input row 1 matches 8-12-4 give quantity 15)

View 3 Replies View Related

Combine Rows Into One

Jul 7, 2009

I have data as follows -
OrderTDF
1gc3
1gc3
2du3
2du3
2gc3
3du3
3du3
3du3

and want to combine lines by order so that data now becomes this -
OrderTDF
1gc3gc3
2du3du3gc3
3du3du3du3

View 9 Replies View Related

How To Merge / Combine 2 Rows

Aug 20, 2014

I've tried to combine rows, but can't seem to do it. I have a spreadsheet with over 18,000 lines of data, and I really don't want to go through each line to add information from another line and then delete that line. Is there a formula that can do this? I have attached a sample of what I have and what I need.

View 8 Replies View Related

Combine Rows With Same Cell Value?

Nov 24, 2013

I have just two columns, one with numerical codes (A), the other ones with text (B).There are duplicate codes with different values, for which I would like to be able to combined their text values on a single row.

A | B
xx | text 1,
xy | text 2,
xz | text 3,
xz | text 4,
xu | text 5,
...

to be formated to:

A | B
xx | text 1,
xy | text 2,
xz | text 3, text 4
xu | text 5,
...

The resulting column B can be with/without space delimiter between the text values (there is a coma after each text value)

View 3 Replies View Related

Macro To Combine Rows

Nov 12, 2008

I have a table which has employee names on the left handside. Next to them are contacts in a region.

View 7 Replies View Related

Combine Rows From ID Number

Aug 26, 2013

I have a data that looks like this

P50101
181.0
89.2
19.94
12/18/06
KK

P50101
177.0
75.9
22.26
11/26/07
KK

[Code] .......

I need to enter this into spss so I need it to look more like this e.g. grouped by the first column

table { }td { padding-top: 1px; padding-right: 1px;
padding-left: 1px; color: black; font-size: 11pt;
font-weight: 400; font-style: normal; text-decoration: none;

[Code] .......

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

Combine Two Columns And Rows Into One Column

Jan 23, 2014

I need two solutions, if possible. One macro and then a "poor man's way" in case the macro breaks down the road. something where I can do it with copy/paste, formulas etc.

I will just use letters and numbers to make it easer.........

Column A might have 10 rows, with 1,2,3,4,5,6,7,8,9, and 10 as the data.
Column B might have 7 rows with A,B,C,D,E,F and G as the data.

I need column C to have 70 rows, with 1A, 1B, 1C, etc. .......through 9E, 9F through 9G as the data.

View 1 Replies View Related

Combine Rows With Same Heading & Have Values Into 1

Mar 25, 2008

I am having 3 rows with same address and want to combine rows with equal names into single and delete remaing two rows

View 3 Replies View Related

Pivot Table Fields Expands Automatically When Updating Data (only Where Data Has Been Changed)

Jan 11, 2013

I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?

It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.

View 1 Replies View Related

Multiple Criteria And SUMPRODUCT (count The Number Of Rows That Have Values Greater Than 10/01/2008 In Either Of Two Fields)

Jan 23, 2009

I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.

View 2 Replies View Related

Combine Rows From Matching Certain Criteria In Two Tables

Jun 9, 2013

I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.

A sample file illustrating the inputs and required output is attached.

I also posted this on: [URL] ....

Sample_file_summary.xlsx

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

Displaying And Summing Data With Duplicate Data Fields

Jun 6, 2009

I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.

Can this even be done?????

Say if the user entered:

Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00

The data in the GL and currency column in the summary box would appear as the following:

Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00

View 3 Replies View Related







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