Merging Results Into Summary?
Oct 13, 2011
I have been asked if I can create a solution to take data from an accounts sheet where 2 columns of data are used to record dates and values and then make a master summary of each account into a separate sheet.
Shown below is an extract of the Main Accnt sheet, and then a copy of what is requried as an end result, there are more accounts to consider in the real sheet. but will be 2 columns only and an account identifier
Main Accnt
ABCD1Account CodeWse12Account CodeESS 122 3DatesAmountDatesAmount402/09/201134501/10/2011453503/09/201136702/10/2011678604/09/201138903/10/2011903705/09/201141104/10/20111128806/09/201143305/10/20111353907/09/201145506/10/201115781008/09/201147707/10/201118031109/09/201149908/10/201120281210/09/201152109/10/201122531311/09/201154310/10/201124781412/09/201156511/10/201127031513/09/201158712/10/201129281614/09/201160913/10/2011315317 14/10/2011337818 15/10/2011360319 16/10/2011382820 17/10/2011405321 18/10/2011427822 19/10/20114503
An indication of the Master Summary is shown here. I have coloured the account code only as an example that isn't required.
Master Sum
ABC1Account CodeDatesAmount2Wse1202/09/20113453Wse1203/09/20113674Wse1204/09/20113895Wse1205/09/20114116Wse1206/09/20114337Wse1207/09/20114558Wse1208/09/20114779Wse1209/09/201149910Wse1210/09/201152111Wse1211/09/201154312Wse1212/09/201156513Wse1213/09/201158714Wse1214/09/201160915ESS 1201/10/201145316ESS 1202/10/201167817ESS 1203/10/201190318ESS 1204/10/2011112819ESS 1205/10/2011135320ESS 1206/10/2011157821ESS 1207/10/2011180322ESS 1208/10/2011202823ESS 1209/10/2011225324ESS 1210/10/2011247825ESS 1211/10/2011270326ESS 1212/10/2011292827ESS 1213/10/2011315328ESS 1214/10/2011337829ESS 1215/10/2011360330ESS 1216/10/2011382831ESS 1217/10/2011405332ESS 1218/10/2011427833ESS 1219/10/20114503
View 3 Replies
ADVERTISEMENT
Jun 11, 2014
I am attempting to write code for a macro that can cycle through about 30 excel workbooks and merge the data. getting the raw data on one sheet is not a problem, but I also need to sort similar data into corresponding columns while merging such that all data from the workbooks with a certain column heading will be in a single column with a single heading on the summary sheet. The problem is that the column headings on the source workbooks are not always standardized, i.e. "F high", "high F". Each sheet has several columns that look like the one below, with a header at the top and a series of numbers. (please ignore the dashes, I just used them to line up the columns in this post, in excel each value is in a different cell)
F Low---F High---V Low
721-----999---804
721-----999---0
721-----999---0
711-----720---786
711-----720---713
My vba skills are very basic, so I have been approaching this problem with a mountain of if..else and InStr commands, which doesn't seem like the best method and isn't working.
View 3 Replies
View Related
Oct 29, 2007
I have the following table for results in 7 races by 8 competitors (NB Not all competitors compete in every race)
Competitor Race 1 Race 2 Race 3 Race 4 Race 5 Race 6 Race 7
........A...........3..........5..........4.........6..........4........1
........B...........2..........4..........3.........4..........6........3
........C...........4..........3..........2.........5..........5........4
........D.......................6..........1....................7.........2........3
........E...........7..........7..........8....................3.........5........4
........F...........1..........2..........6.........1..........8........8........1
........G...........5..........1..........5.........2..........1........7.......5
........H...........6..........8..........7.........3..........2........6........2
What I'd like to have is a table with summary results by the follwing criteria:
Most 1sts:
Most top 3:
Most bottom 2:
Lowest average position:
Highest average position:
What formulas would I have to use to get each of the 5 results?
View 9 Replies
View Related
Jul 2, 2009
Attached is five months of dummy sales data for eight products. My objective is to filter this data with date ranges (using Column A in Data sheet), and have the corresponding pie slices (shown in Pie Chart Sheet) be the summation of the Products grouped by their designated letter. Currently, the result I am getting is 6 pie slices for "Product A" and this is wrong. I want to see "Product A" show up only once in the LEGEND and only as one aggregated pie slice. The same holds true for the rest of the Product Letters.
I want to avoid using Pivot Tables. My objective is to keep my layout clean and simple. I would like to know if anyone out there could help me figure out a formula that will automatically catagorize all of the resulting filtered data by the Product Letter they share. Once the formula categorizes or groups together all of the resulting filtered data by Product Letter, I would like the formula to calculate a summation for each Product Letter category. After the formula finishes the summation task I would like the formula to feed these results to a Pie Chart. The resulting chart I would like to see will show individual pie slices for each Product Letter resulting from the date range filtering operation performed on Column A. The resulting letters can be anywhere from "A" to "H". My goal is to only have one individual pie slice representing each of the Product Letters resulting from a Date range filter. I thought such a task would be as easy as baking a pie, but it's turned out to be beyond my Excel skills.
View 9 Replies
View Related
Feb 27, 2009
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
View 8 Replies
View Related
Dec 23, 2013
excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
View 2 Replies
View Related
Jul 27, 2006
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies
View Related
Mar 7, 2014
I am merging the following two IF formulas together.
=IF(ISBLANK(M4),"NOT RECEIVED",0)
and the second formula I want to merge is
=IF(A2+5>=M2,0,"LATE")
View 4 Replies
View Related
Jul 8, 2008
I have an excel sheet with first and last name and I have another column called username.
The username will be "firstnamelastname" all lower case and no spaces.
I know I can use C1=lower(&A1""&B1)
View 13 Replies
View Related
Aug 2, 2009
I'm compiling data to be transfered into a report program but the programe can't deal with any Excel Formulas so the data has to appear 'as is' so to speak. I have 2 columns of data (roughly 1200 rows) and I need to merge the two together but i can't have the formula in the merged cell......
View 4 Replies
View Related
Sep 10, 2009
I have 2 sheets
1st sheet data
Job_idHost_NameOutcome STEP_INDEX
742762Blank success Null
742762QCTCRMBLD07success 0
742762QCTCRMBLD02success 1
742762Blank skipped 2
2ND sheet data
Job id RunTime QueueTime
742762 52 .4562 0.12365
Now i have to insert the 2nd sheet data into the first sheet whereever the step index is NULL
So Basically my output should be like below:
Job_idHost_NameOutcome STEP_INDEX RunTime QueueTime
742762BLANK success Null 52 .4562 0.12365 742762QCTCRMBLD07success 0
742762QCTCRMBLD02success 1
742762BLANK skipped 2
View 7 Replies
View Related
Dec 3, 2009
I am looking for some tips to merge the 2 rows into 1 row with all data transferred. I have tried the following:-
=IF(MOD(ROW()/2,1)=0,INDEX($B$1:$B$100,ROW()/2),INDEX($A$1:$A$100,INT(ROW()/2)+1))
However, it returns "0" when cell is empty. Your help is greatly appreciated. Attached a file for better understanding.
View 10 Replies
View Related
Jul 26, 2013
I have 29 inventory sheets that are identical. I need to merge them in to one master work sheet.
View 2 Replies
View Related
Jul 17, 2014
I have 3 Worksheets in Excel I would like to merge into a new worksheet. Worksheet A, B, C. I have 133 rows of information on each sheet.
I would like to merge them in a particular order. I would like to take Row1 of Worksheet A, then merge Row1 of Worksheet B, and then merge Row 1 of Worksheet C.
So on the new Worksheet I have 3 rows of combined 3 worksheets. The would like to repeat this for all rows 2, 3, 4 etc. till will get to row 133 or end.
View 1 Replies
View Related
Jan 19, 2009
I have a column with data, the cells go data, blank cell, data, blank cell, data etc.
The row count will vary.
What I need to do is go through the non blank cells in the column and merge the them with the blank cells beneth them.
ie A2 = Apple
A3 = blank
A4 = Pear
A5 = blank
run the code to get
A2 and A3 = Apple
A4 and A5 = Pear
View 9 Replies
View Related
Feb 10, 2009
I collect data from a number of tools for our server and switch info. I don't have a problem merging that data. My problem is trying to get that data into a working format.
-- I have a server that has dual network connections.
-- This data resides in different rows, and each has slightly different data that I need to preserve.
-- I want to merge these two rows into one pre-defined output layout on a separate sheet without loosing any of the data.
Some other things of note. Not all of the servers have duplicate entries, but still want that data on the output sheet as well.
I have attached an example of what I have to work with and what the output sheet that I'm trying to get to looks like.
View 8 Replies
View Related
Oct 15, 2009
I have two worksheets in the same workbook that need to be merged into a third worksheet. I need to take all the data in worksheet "On Hand" and bring in from the worksheet "Transactions" the data in columns E & F (into the new worksheet "Excess Inventory"). There will be item numbers (Column B in both sheets) that will only be in the "On Hand" sheet. I have entered data in the sheet "Excess Inventory" to show what I am trying to do.
View 3 Replies
View Related
Jul 4, 2007
I have an excel file with the same columns of data for different years (each year's data is in a different sheet). Each of the columns corresponds to a different survey question asked to the same farmers year after year. These farmers have been identified by ID number in the first column and I would like to consolidate the information from all the years into a single sheet.
The problem is that there are many farmers who dropped out of the survey or were added to the survey over the years. So, for example, in year one you may have data for the following ID numbers: 1, 2, 3, 5, 7, 8, 9 And by year 4 you may have data for the following ID number: 2, 3, 4, 5, 9.
View 12 Replies
View Related
Dec 16, 2008
merging two Excel files. 2009 Item List.xls file has two columns Part number and Description. Price.xls file has two colums: part number and price. I need to merge them into a file that whould have three colums: part number, description, price. There 6712 rows in 2009 Item List.xls and 4743 rows in Price.xls file. So I need them matched based on the part number, and have the cells blank for the part numbers for which the price was not assigned.
View 9 Replies
View Related
Mar 21, 2012
I'd like to combine two VBA subs existing on a single sheet. Since they can't duplicate I need some sort of a switch statement...?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("E4:E100")
For Each cell In Target
[Code]....
View 5 Replies
View Related
Dec 20, 2006
i would like to merge two columns as explained below
for ex
the 1st column is:
1
2
3
4
5
the 2ed column is:
1
2
3
4
5
the merged columns should be:
1
1
2
2
3
3
4
4
5
5
View 9 Replies
View Related
May 25, 2007
I have data in two separate cells as follows:
Cell A1 = 7890
Cell A3 = 1
In Cell A4 I want to merge the figures as following figure:
789.01
What type of formula would I need to create for this to work for me?
View 9 Replies
View Related
Oct 1, 2007
I have given an example of below of what i am trying to achieve. I am seeking to match up the region name with the project id and to have the outocme put into expected output. i have done two examples below. The problem that i am facing is that "Asia" as you will see has more than one project info. (the list is quite long)
ProjectInfoProjectIdRegionRegionNameExpected Output:
CW28915NL - Netherlands
CW28915HK - Hong KongNL - Netherlands ; HK - Hong Kong
CW30695Asia
CW30695America
CW30695Middle East / Africa
CW30695EuropeAsia ; America ; Middle East / Africa ; Europe
CW32739Middle East / Africa
CW32739Europe
CW32739Asia
CW32739America
View 9 Replies
View Related
Jul 17, 2008
I need help in merging sheets in 4 workbooks, meaning bringing the sheets in all 4 workbooks into 1 workbook.
View 9 Replies
View Related
Nov 13, 2009
I've created a time sheet that we fill out daily. I currently have two macro buttons on the sheet. One "submits" the sheet by creating a .pdf, saving a copy and then emailing it to the boss. The other closes the time sheet without saving so that it doesn't carry over today's data to tomorrow. My question is, can I assign one macro to do both?
View 9 Replies
View Related
Aug 18, 2005
I need to merge two spreadsheets....
1 spread sheet has 1500 filenames store in Column A and the another spreadsheet has all filenames(Near 3500) in column A and their properties in the next three columns.
Can anyone teach me how to automatically match the same filename and merge together the data and put to sheet 3?
so in sheet 3 I can have the following
Column A = Filename | Column B = Property 1 | Column C = Property 2 | Column D= Property 3
View 4 Replies
View Related
May 17, 2006
I have 2 tables in excel that i need to merge into one, Table 1 has 10 colmns and Table 2 has 7. Both tables have as column 1 a heading of Material, this is the column i need to compare as table 1 does not contain a material description field, but table 2 does, the bold headings are the key ones, but other column information is required. I have attached a sample ss to show what i am trying to do.
Table 1 Col Headings
Material
MTyp
MS
R
Clt
L/O
Created on
Created by
View 2 Replies
View Related
Jun 15, 2006
I have two seperate graphs in two different workbooks. Both graph's are identical in the setup, they have the same X axis and Y axis value. They are identical in every way except for the varying in percentages. This graph was originally setup in that it would calculate the results for a period of a week at a time. Is there a way to merge the two graphs thereby showing the results for a two week period?
View 4 Replies
View Related
Jul 20, 2006
There is no unique common key to relate records in two work sheets of same workbook. Therefore, the first step in generating this report is to create a common key that can be used to match records in worksheet 1 to worksheet 2 records. Common key is generated by concatenating few of the columns common in both the data sets (say something like brand + Site + End Date + Last name + First name)
The matching run should match the pseudo keys in the two datasets, and merge the records into one file using the following criteria
Event.key1 = ECB.key2 and Event.act_psrv_amt = ECB.folio_amt
Used Vlook up function but it takes really long time to return matching record (note that I want to return whole of the matching record) as we are dealing with huge data.
attached is the sample data
View 9 Replies
View Related
May 9, 2006
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
View 9 Replies
View Related
