I have a data feed that gives me a summary of a set of data. I want to be able to work back to what would be the original data (this unfortunately isn't available). The attached excel file as an example of what I am trying to do.
I am not too sure about the working with the dates etc.
Date ModelStationLot_SizeSampleReject
04/7/07APCA771
04/7/07CPCA64320
04/7/07BPCA36320
04/7/07BPCA441
04/7/07CPCA110
04/7/07AB/B880
How can I quick summariz these data by VBA ? as below result.
Date ModelStationLot_SizeSampleReject
04/7/07APCA771
04/7/07AB/B880
04/7/07CPCA65330
04/7/07BPCA40361
This is just my sample data. The real data is more complicate than this. I want someone suggest me about VBA code.
I am trying to summarize data for my boss,
She has a spreadsheet with all of the employees of the company listed. Each employee is associated with a cost ceter (there are several hundred employees, and about 90 cost centers). Each month we count how many people are in which cost centers. I am using a sumif equation, and that works well, however, we keep the historical data, as well as the budget data in the same spreadsheet, so each month the "sum_range" column needs to change (ie column 4 for feb, 5 for March). I can use a find and replace to address a different column in my equation, but my boss would prefer something simpler.
I use INDEX and MATCH equations quite frequently now and they are invaluable! However what I'm trying to do now doesn't quite fit. I've attached a small sample of my workbook. The sheet called 'data' has three columns of data that I need to split into a grid (called summary). I'd know how to do the opposite of this, as in changing from a grid to a list by using an index and match. But I'm not sure how to do this the opposite way around?
View 3 Replies View RelatedIm creating a workbook for my office. The workbook has a sheet for each user. The user chooses a channel from the drop down list, then complete the next 5 fields (must add to 100). I have a totals sheet where I want to summarise the data from the whole workbook as per the template on the totals sheet. Ive attached my example.
View 3 Replies View RelatedI have the attached spreadsheet that has 122 columns I would like to create several common button at the top left hand side to summarize my information. The columns that I would like to are name, id, partner.
View 2 Replies View RelatedI have a worksheet that look something like the excel.jpg but hundreds of rolls instead
I wonder if there some way i can convert it into something like exel2.jpg without having to do it manually
if excel is not capable can i use access to do it ...
I have a workbook where employee scheduling is done on a monthly basis in 15 minute intervals. It is laid out like this:
******** ******************** ************************************************************************>Microsoft Excel - Key Support Services 090813.xlsx___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2C2D2E2F2G2H2=ABCDEFGH2Time8/1/20098/2/20098/3/20098/4/20098/5/20098/6/20098/7/200938:00 AM 48:15 AM 58:30 AM 68:45 AM 79:00 AM 89:15 AM 99:30 AM 109:45 AM 1110:00 AM 1210:15 AM 1310:30 AM 1410:45 AM 1511:00 AM Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The times go from 8 AM - 8 PM, and all of the cells within the range B3:AF51 are drop downs with the employee names listed. They are conditionally formatted so that we can easily see who is scheduled to work when, and on what days.
The problem that I have is that I like this layout for inputting the data because I can see it all very clearly, but it is not good for archiving or saving past months data. I want to be able to take that view and change the format so that it can easily be summarized in a Pivot Table for all of the historical information.
So, I need to take the date and time and format both of them into a column, put the client name (will be the sheet name) in the next column, and then the name of the staff that worked during that time.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateTimeClientStaff28/1/20098:00 AMClient1Employee138/1/20098:15 AMClient1Employee148/1/20098:30 AMClient1Employee158/1/20098:45 AMClient1Employee168/1/20099:00 AMClient1Employee178/1/20099:15 AMClient1Employee188/1/20099:30 AMClient1Employee198/1/20099:45 AMClient1Employee1108/1/200910:00 AMClient1Employee1118/1/200910:15 AMClient1Employee1128/1/200910:30 AMClient1Employee1138/1/200910:45 AMClient1Employee2148/1/200911:00 AMClient1Employee2158/1/200911:15 AMClient1Employee2168/1/200911:30 AMClient1Employee2178/1/200911:45 AMClient1Employee2188/1/200912:00 PMClient1Employee2198/1/200912:15 PMClient1Employee2Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
From there I can take a completed month, and quickly run some code to make the change, and add it to the historical tracking where it is all summarized.
I have a worksheet that has similar data but some maybe different lengths. What im trying to do is to be able to have them all in line so where the diagram no. is on the worksheet they are all lined up. On the excel sheet attached I want TURN NO in column J to line up with turn no in Column A each time.
View 2 Replies View RelatedI have a stacking program but it does not keep the first column associated with the data that is stacked as well as the first row. This is how the database looks now:
CREATE TABLES LIKE BELOW?July '07August '07September '07
HR #55551341,929
HR #57217311,653
HR #102322,8981,988
HR #98221,5571,097
this is what I need:
CREATE TABLES LIKE BELOW?HR #55551 July '07
HR #5721 July '07
HR #10232 July '07
HR #9822 July '07
HR #55 34 August '07
HR #57 731 August '07
HR #102 2,898 August '07
HR #98 1,557 August '07
HR #55 1,929 September '07
HR #57 1,653 September '07
HR #102 1,988 September '07
HR #98 1,097 September '07
The current VBA prgram just stacks the columns in one column going from right to left.
I have included some example data and what I need to be able to show in the attached file.
I have tried using pivot tables but need to keep the totals static when I hide a column.
Can anybody suggest another way of achieving what I need? I need the solution to be dynamic (like a pivot table) but need to hide certain data and have totals including the hidden data.
I have created a simple order form to take customer orders . I would like to summarize the orders on a summary page .(The order Form will be very long so i don't want to print the entire document every time .
I have started to create a summary page using information from a similar post on this forum. I am having trouble creating a summary when the products on my order form have multiple sizes rather than just one
It does everything I need it to do but I would like it to sort 3 columns and then merge the data. I have included a sample with a "WishReport" tab the shows what I am looking for. If possible (not imperative) I would like it to add a "unit cost" column and provide a grand total.
Option Explicit
Public Const SZ_INPUT As String = "SheetData"
Public Const SZ_OUTPUT As String = "Report"
Public Const I_FIRST As Integer = 0
Public Const I_LAST As Integer = 1
Public Const I_DATA As Integer = 2
Public Const ROW_HEADER As Long = 1
Public Const ROW_DATA As Long = 2
Public Const COL_FIRST As Integer = 1
Public Const COL_PROJECT As Integer = 2
Public Const COL_LAST As Integer = 5
Sub CombineProjectData()
Dim lInputRows(I_FIRST To I_DATA) As Long
Dim lFirstInstance As Long, lOutputRow As Long
Dim szData As String
Dim iCurrColumn As Integer
Call FindRowArray(lInputRows(), , COL_PROJECT)...............
Is it possible to consolidate data from various worksheets into a single worksheet using multi reference points or lookup_values?
For example, I have 2 worksheets named Section A and Section B in which I have detailed monthly expenditure details (e.g. overtime, allowance, uniform, etc) for each sections, respectively.
Then in the 3rd worksheet, called Combined, I have 2 columns - first is 'MONTH' and second, 'SECTION'.
Now, suppose I have a 3rd column in which I want to pull over the overtime expenses for both sections. That means, my VLOOKUP must first look for the SECTION worksheet, then find the MONTH before returning the value found in the overtime column.
How can I do this?
I have a worksheet with several columns of data that consists of month to date billing, cost, profit, etc. that are tied to job numbers.
I currently have code ( AdvancedFilter) that seperates this data judging by the first two numbers of the job number. For instance it will take all job numbers that start with 11, copy them to a worksheet, print it and then do the same for 12, 13, 21, etc..
I am trying to write a vba code which is attached to a button, that will do the follwing.
Take the data from one sheet and summarize it onto another one.
I'm not sure if I am going about it the correct way. My approach was going to be to copy all the data onto another page, sort it by columns A, B and C. But I've run into problems, I've attached a sample worksheet showing what I'm using and how I want to use it.
As well this is part of my code I started coming up with, am I on the right track?
[EDIT]
Ok so I've been playing around and thanks alot to bryce for making copying simplier, this is what I've gotten for my code so far (its a lot simplier than before)
Sub uTotals()
Dim wsData As Worksheet, wsResult As Worksheet
Dim DataRow As Long
Dim x As Integer
Set wsData = Worksheets("Material")
Set wsResult = Worksheets(" Totals")
DataRow = wsData. Range("A65536").End(xlUp).Row
For x = 1 To DataRow
wsData.Range("A" & x & ":" & "C" & x).Copy wsResult.Range("A65536").End(xlUp).Offset(1, 0)
wsData.Range("F" & x).Copy wsResult.Range("D65536").End(xlUp).Offset(1, 0)
wsData.Range("I" & x).Copy wsResult.Range("E65536").End(xlUp).Offset(1, 0)
Next x ...
I need to change the layout of data within a cells. Attached is a sample of what I need to do
View 9 Replies View RelatedI have a spreadsheet that tracks hours for employes and I need to get the data in a different layout to import to a database i'm building. The example spreadsheet has 2 tabs, the first is the format it's currently in and the second in the one I need it in.
View 4 Replies View RelatedI would like to know if there is a way to change the format of external data? Instead of the default:
the sheet is very long and monitors the sales activities of each individual every day extending vertically down the sheet until the present day.
my first priority was to get the information recorded but now i am wanting to use it.
how would an expert arrange this data to make analysis easier going forward?
ultimately i will be wanting to look at individuals, groups of individuals, certain types of activity etc within various time periods.
Is there an way to make an YES and NO button where when i click YES it pop-up a box asking for confirmation on the action and if you confirm it then the data wrote into A1, A8, D1, D8 will be saved into "Product Exit" worksheet then the data into A1, A8, D1, D8 will be cleared.
aswell as a NO button but the NO button will save into Product not sold IF confirmed TRUE and then cleared and if confirmed FALSE will just clear A1, A8, D1, D8. if it cant be done with the confirmation can it be done without it example ?
I would like to state how fantastic a resource this forum has been to me as a beginner. So keep up the good work! The issue I have is that my spreadsheet contains one column which shares two types of data: Component Type and Tag Number. What I would like the script to do is:
1. create a new column
2. move the Tag Number data from its current column into the newly created column
3. ensure that the data is still on the same row as it was previously
One thing to bear in mind is that the tag numbers consists of various formats (spaces/no spaces), but always begin and end with brackets - "(XTU U-532-934)". I have included and example of the current state of the data and what I would like to look like after.
I would like to change the data layout from the "Original Table" to the "Final Table" as in attached file. Belinda_June-25-07.xls. How do I use VBA to do the change? In my original table, I may have several hundred rows and more than 36 columns for the time periods.
View 3 Replies View Relatedsummarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.
View 4 Replies View RelatedI have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).
I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?
I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:
ID
Status
Joined
Name
Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:
Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".
There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.
I have three lists and I want to summarize them in one table. I can do this by formula or using Data validation by position. Who is the best way? There is a difference between them?
View 3 Replies View RelatedI have a project that has several worksheets of "summarized" data all pulled from one worksheet. The data worksheet is run through an external program and pasted into the workbook to update the information.
To this point I have used tons of index match array formula's to pull the information but I've found this to be extremely inefficient because there are hundreds if not thousands of formulas, and it takes several minutes to update. The file is also around 3MB which is way too big.
I was thinking about using VBA to simplify the worksheets, but then I came across an article about D_Functions and I'm thinking they may be the solution to my problems.
However there is one catch...I'm not sure how I would build these formulas to work in my worksheets. I understand the principle behind the D_Functions, but I can't seem to grasp the concept of integration and making it work in my scenario.
I've attached an example of this workbook, it has most of the forumla's deleted out to reduce the file size, but it should give you an idea of what I'm doing. Once you open it you'll have to unhide some columns to get to the nitty gritty of the sheet.
Feel free to take a gander...I'm open to any suggestions you may have at this point (except to trash the whole project hehehehe ). If you find a solution please explain the concept behind it. I really want to learn how to make this work rather than plugging in someone elses formulas.
Macro or formula to build a table from a data.
The data is in the following format in 4 columns: A (Customers' names), B(type of transactions: invoice,payment, Credit,Check), C (date), D(Amount).
Data is for the whole year by monthly.
I would like to build a table like this:
Column (A) 10 names of customers and in next 12 columns by month amounts and types of transactions.
I am using Excel 2010 on windows 7 PC. I work for a railroad and one of my coworkers gets data once a week about how many carloads we ship to a certain company. They save each week in its own worksheet so at the end of the year they will have 52 worksheets worth of carload data. What they would like to do is take these 52 worksheets and then combine this data onto one worksheet so they can see all of it going from week 1 to week 52.
Here is what 1 week's worth of data looks like...
Here is what the 52 week summary page looks like...
And here is what the summary page looks like with all of the data on it....
Although we have compiled all of this data, it was done by copying and pasting data from 52 different worksheets (took some time as you could imagine). So, my coworker asked me if I knew of a faster way to do this. After spending multiple hours on the net researching this and trying a bunch of different options (Data Consolidation, Summary/Array Functions, and varying Macros) I have not been able to produce a worksheet that takes all of the data and displays it like the last picture above. Because I only know the very basics of excel I'm sure I'm probably doing something wrong. Trying to decipher the code for macros hasn't been easy and although I got data consolidation to work it was adding up all of the numbers instead of taking them from one sheet and placing them on another with all of the worksheets' data side by side.