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


ADVERTISEMENT

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

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

Combine Data From Multiple Sheets With Conditions

Dec 27, 2012

What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.

What I am after is, when I combine the data, from all sheets into the one, the data should be

1. Automatically Sorted by Patient Name

2. Group, results of the same patient from all sheets one below the other in seperate rows

3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).

Page 1 (Page 2 and Page 3 are exactly the same)
Patient Name
Patient Number
New Mole
Clinically suspicious
Changed from mapping Photo
Level of Suspicion: Score
Monitor

[code].....

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

Using Data Validation To Create List Dependent On Multiple Rows?

Jul 28, 2014

I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)

for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked

View 12 Replies View Related

Combine Two Tables Which Change In Rows Depending On Input Of User Using VBA In Excel?

Apr 13, 2014

i have a macro which takes the results generated and puts them into a table and filters the data so that zeros does not show and arranges the table from largest to smallest. Now i have a problem where i need to select the power, fuel oil consumption, weight and area from one table and the other power, fuel oil consumption, weight and area from the other table and combine them together so that i can have all possible combinations of the data, but i don't want the zeros to show in the combinations. i need the combined data to show on a separate sheet. The data of table 1 varies with rows as shown:

The Data of Table 2 varies with rows as shown:

I would like to achieve a result like this in the following columns and rows without having to input them manually:

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

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

Excel 2003 :: Hiding Rows Based On Conditions On Two Worksheets?

Jan 4, 2013

I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.

Sheet1 sample:
Type
Selection

[Code]...

The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).

If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.

So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.

Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.

I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!

View 3 Replies View Related

Excel 2003 :: VBA / Hiding Rows Based On Three Conditions On Two Different Worksheets

Jan 14, 2013

I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.

So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.

Sample of Sheet1
Country
Scotland

Location
Glasgow

Start Date (dd/mm/yyyy)
30/04/2013

[code]....

So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.

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

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

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

If Formula To Combine Two Lookup Conditions?

May 23, 2014

Combine two lookup/IF formulas:

[Code]......

So if formula 1 conditions aren't met then it by default would result in formula 2 conditions being met.

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

Excel 2007 :: How To Combine 2 Sheets Into 1 With Different Data

Jun 13, 2013

Here is basically the problem, I have 2 sheets and want to copy/ paste them like this:

Sheet 1
Name of a product
Data 1
Data 2
Data 3

[Code]....

Can this be done in Excell 2007?

View 5 Replies View Related

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

Excel Formula To Retrieve Data From Two Cells With Certain Conditions?

Jul 4, 2014

cell: A1 = 1st data
cell: B1 = 2nd data

cell: C1 = 3rd data

1. If A1 = 85 (exact 85 only)
and if B1= < 25
then c1 = 0.21

[Code]......

View 5 Replies View Related

SQL Query That Will Populate Data From Access To Excel Sheet Using Conditions

Jul 1, 2014

I have an Access table which has following fields:

ScanDate Number DataType
Type Number
Type1 Number
Type2 Number
BatchNo Number
Cases Number
Pages Number

Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.

See attached the Excelsheet where the data should be populated to. WBCount.xls‎

View 1 Replies View Related

Excel 2010 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value

[Code] ........

View 1 Replies View Related

Excel 2010 :: Auto-populating Calendar Based On Conditions From Data Table On Another Sheet?

Apr 26, 2014

I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.

On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.

On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".

View 2 Replies View Related

The Sum Of Multiple Rows Dependent On Header

Jul 18, 2008

I have a worksheet with 30 sets of 3 columns, each column has 20 to 30 rows in use for the same realitive data. I want to get the sum for each 1x across 30 sets of the columns. I am checking vs if it says Projected or Actual. My Visual Basic macro ability is very limited at best. I have a totals row for each row of data so i can see totals for 1x over 30 days, 1.5x over 30 weeks, and 2x over 30 weeks.

Here is the basic idea of what i am dealing with:

A B C
Projected (merged across A, B, and C)
1x 1.5x 2x
8 4 1.5
7 0 0
8 2 0
8 4 5

Again, this represents one day, and I am trying to track at least 30 days across 90 columns. Also there are anywhere from 20 to 30 people being tracked at any one time so this continues to many more rows depending on the job.

Please help, it would be greatly appreciated.

View 9 Replies View Related

Same Dependent Dropdown List In Multiple Rows

Jul 4, 2013

I have been asked to edit a worksheet for a client, they want me to create a dependent drop list using existing data from three columns.

I have created a dynamic named range and used and index to create the drop down list, which seems to work correctly. I run into a problem with I try to add the list to more than one column, as it obviously pulls in values from the Helper sheet I have used.

When the user tries to select values in row one after making a selection in row two, the values available for selection are incorrect.

Is there any way to have a three column dependent drop list that can be used in more than one row?

I have uploaded the file here: ownCloud

The information on the tab named 'Cabling Ducts' is used in an index on the 'Helper' sheet to remove duplicate values. The drop down list columns appear in 'CS 1' under the headings 'Cable Type', 'Size (mm)' and 'Cores'. Helper sheet is self explanatory.

View 1 Replies View Related

Deleting Rows That Have Dependent Cells. Prevent !#REF Value

Oct 27, 2007

Deleting rows that have dependent cells.

I have one sheet that uses a very simple formula for copying certain values from one cell to another sheet =VALUE(Sheet1!B5).

My problem is that rows have to be deleted in sheet1 as the information eventually becomes expired and the following is displayed in my cell in
sheet 2 =VALUE (Sheet1!#REF!) Is there a work round this, to prevent the formula showing a #REF! Value.

Row 5 is replaced and the same formula has to be re-entered. Excel 97 used in our office unfortunately.

View 10 Replies View Related

Excel 2013 :: Averaging Rows Of Data Within Larger Blocks Of Datasets - Skipping Rows

Jul 16, 2014

I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.

Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.

I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.

Is there a formula/script for me to skip the correct number of rows to the next data block?

I have attached my spreadsheet to this thread. I am using Excel 2013

FeS_Kinetics.xlsx

View 1 Replies View Related

Dependent Drop Down List With DEPENDENT DEFAULT VALUE

Jan 22, 2010

Situation:
I have two drop down lists 1) Country and 2) States/province

Country has list: [US, Canada]

If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list

Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"

Probem:
Now I have "Canada" and "Florida" selected in the sheet

Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?

View 11 Replies View Related







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