Excel For Data Aggregation And Then Disaggregation Using Coefficient
Oct 9, 2011
I have a problem in Excel for Supply Chain. I want to aggregate a set of data, change the aggregated data and then disaggregate back to constituents using the same proportion in which they were aggregated . I can do aggregation using pivot tables but then I cannot change and do the deaggregation.
Initial Disaggregated data
--------------------
Group Item# Qty
BALL423242254BALL534534555BALL4232422442BALL543535433
BALL534535322BAT53453532BAT5343533222BAT535353222
CYCLE432424222CYCLE4234244222CYCLE43243241313
CYCLE42342257676CYCLE43244226565
Aggregated data
-----------
BALL606BAT246CYCLE15798
Change in Disaggregated data
-------------------------
BALL1000BAT3000CYCLE5000
After the change, I should be able to disaggregate the data using the same co-efficient I used in aggregation
View 1 Replies
ADVERTISEMENT
Jul 26, 2006
I have a workbook which has sales data in one worksheet and inventory data in one worksheet. There around 50 workbooks I get each week one for each customer. The column headers are same for sales and inventory in all the sheets. All the 50 files are in the same folder.
Is there a way to put the sales data of all customers in one worksheet and inventory in one worksheet. Currently I am doing it manually.
View 3 Replies
View Related
Jul 23, 2007
I have the monthly data stores column by column, e.g. Column B for Jan 07, Col C for Feb 07 etcetc, so every month I have a new column added to the end then I have some statistical analysis table which calculates such as annual return, standard deviation etcetc
How can I make a button which I can click then it can update to replace the formular with the latest column? for example, =CORREL(B2:B10, G2:G10), col B contains Feb 07 data and G contains June 07, let's say I add another column for July 07 which will be column H and I would like by clicking the button then all formula with col G will be replaced by col H?
View 8 Replies
View Related
Jan 3, 2010
I have a very large spreadsheet, a few columns and rows I attach..
in col a we have the time of the event..in column b i have a numerical rank for the
importance of factor 1,in column c i have similiar for factor 2.
What I want is the correlation for each event between col b and col c using the rsq function..i have no problem using this function..but i need the result for each event produced in col d in this same sheet and printed on each row and since I have thousand of rows i need a formula I can fill down
see attached sheet
View 5 Replies
View Related
Sep 7, 2012
I have data in the following format-----
Name : XYZ
City : ABC
Place : sdfg
Error :
price : [X]
cost : [ ]
time : [ ]
[code]....
I want only name, place,desc and under error, i want that type which is marked cross in the brackets(In the above example it is price ) .
name, place,desc,error should be pasted to separate columns in second excel sheet.
View 1 Replies
View Related
May 23, 2009
Is there any macro that can be used to convert data which have been converted from PDF to Excel. Currently I'm using a traditional way by pressing F2 and enter to convert the data to excel format. It's tedious ( since the data range is quite big) and crammed my finger.
View 9 Replies
View Related
Oct 19, 2012
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
View 2 Replies
View Related
Apr 22, 2014
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
View 2 Replies
View Related
Jan 8, 2014
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
View 2 Replies
View Related
May 5, 2014
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 Replies
View Related
Mar 2, 2012
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
View 5 Replies
View Related
Dec 27, 2012
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
View 3 Replies
View Related
Nov 3, 2013
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
View 2 Replies
View Related
Jun 16, 2014
What I would like to do is create a userform where I can input a case reference number and click a command button which searches the CSV/TXT file for any notes for the specific reference number and then returns all notes (seperated by a blank line) in the textbox within the userform in the posted/saved format.
I can find lots about importing to excel but nothing about importing data to userforms.
CSV file added for format. The userform contains two text boxes, one blank for import and one for completing the case reference number (format on CSV file. Line item 1).
i.e. if I searched by SL-001-0155648 I would get the below (It's not code but I thought the best way of showing what I mean):
[Code] .....
Attached File : Notes Test CSV.txt
View 15 Replies
View Related
Mar 3, 2014
I have created a userform with multipage, has two page that add new record in a excel sheet. Data has a unique reference no.(TxtRef.Value) for each record. I am trying to add a button to load the added data for a specific record using reference no back to userform so that it can be updated and overwrite back to the sheet in the same row, So far it is adding new record correctly. I do not know how to populate all the fields of the of an existing record and overwrite it back to the same row instead of adding a new record. Below is my codes
[Code].....
View 1 Replies
View Related
Jun 1, 2011
I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
View 8 Replies
View Related
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
May 12, 2012
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
View 9 Replies
View Related
Oct 2, 2013
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies
View Related
Feb 28, 2014
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
View 7 Replies
View Related
Mar 28, 2014
I have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
View 9 Replies
View Related
May 17, 2014
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
View 1 Replies
View Related
Dec 20, 2012
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Aug 6, 2013
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
View 3 Replies
View Related
Jun 18, 2014
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
View 1 Replies
View Related
Dec 7, 2012
I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.
I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.
View 1 Replies
View Related
May 8, 2014
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
View 3 Replies
View Related
Mar 12, 2013
IIn my example I have result data from the 2013 USPGA Tour, ordered by player (column A) with subsequent columns detailing their finishing position in each event.
I simply want to condense each player's performance data into one row - as doing it by hand after each event is very time-consuming.
Is there a way of using a simple formula, macro or pivot table (or whatever) to merge each player into one row, but keep each column in the same position (ie to correspond to each weekly event).
View 4 Replies
View Related
Feb 17, 2012
I have a workbook (excel07) that I use to import data onto our purpose built database. I have some macros to sort the data and send it to the correct columns however the one stage I still have to do manually is assign each row its own unique sorting Code (in a bespoke column "B2:B999").
These are objectives and the code should contain three seperate parts (part 1a and b do not need to be seperated);
1a. The characters "PO" should be the first in the code (for sorting purposes)
1b. The persons initials (first letter of the first name and last name) found in column J2:J999. [They are sorted by name so It would also be good to add a number after the initials seperated by a space incase of multiple objectives however I could defintly live with typing these in manually]
e.g. "Joe Blogs" second objective = JB02
2. The numerical part of the stategic objective they have selected found in column H2:H999. [incase you need this these range from 1.1-1.4, 2.1-2.5,3.1-3.6 and 4.1-4.5 all with a short text strings after which shouldnt be included in the code]
e.g. "1.1 We are Committed to being nice" = 1.1
3. The year they are applicable for which will all be "12/13" however I will need to change this next March.
Therefore the final code should appear as "POJB02 3.1 12/13"
There is a 16 Character limit on these codes but all of that information is needed for sorting, filtering and report generation, the spaces could be replaced by "_" if that makes things easier.
View 5 Replies
View Related
