Im 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 RepliesI have a requirement where I need to summarize multiple work sheets. And each work sheet as different range. Column names are same in each sheet but number of rows in each are different. Like consider there are 3 sheets with employee details. Each sheet has Employee Name, Employee Number, Employee Location. But in first sheet as 10 employees and second sheet has 20 employees and third as 25. So the requirement is I need to summarize all employees.
View 1 Replies View RelatedI want a summary list of data from all columns of worksheets that contain the target data and only where the value in col D is 1. So, in the attached example, the data to be checked is on Sheet1 and Sheet2 and the result should be shown on Summary sheet. There will be some tabs that don't need to be checked for the data so it's not a case of check all other tabs except for Summary.
View 14 Replies View RelatedI have a workbook that we use for our internal customers to use to place orders for equipment. There are five worksheets within that file that outline the different products. The requestor will indicate what items they want by putting a number in the quantity cell that is appropriate for the product. This could be using a combination of all five worksheets for the one request.
What I want to do is to create a summary page that only extracts the information from the other worksheets that has a value marked in the quantity spot only.
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 RelatedI 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.
I 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 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 ...
summarize 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.
1) Background Info
We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next.
The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea.
Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are:
- predicted occurrence of various species (given as a percentage for each species)
- observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk)
2) What We're Trying To Do
We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to:
a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet)
b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet)
3) The Problem
Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to:
a) open the attached workbook, and go to the worksheet named "Data"
b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.)
c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself:
- Column A contains the observed occurrence (a positive result is indicated by an asterisk)
- Column B contains the predicted occurrence
- Column C contains the species name
My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck.
How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin.
To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.
I have an excel file having the month wise model cost breakup e.g.Prod.Qty,CKD,Local etc.My problem is that I want to make a summary in a separate sheet showing the Input i.e. Model Code.If I give that input then automatic summary should be prepare.
Please refer attach file: Costing Table.xlsx‎
I this file,having 2 sheets.One is details and other is Summary sheet.
focus the cell no.C4 in the summary sheet.When,I give the model code then the summary should be ready from the details sheet data range, month wise.
I have attached a spreadsheet of an example of a database with a pivot table and a desired report
Can anyone give me a pivot tabe from my database that looks like my desired report.
I am willing to seperate the database into two seperate databases with different transaction types if necessary.
I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.
For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155.
In each instance it is just the "model" detail I need to capture (column D)
I have two questions:
a. Check the code below:
Dim news1 As String, news2 As String
news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie
I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.
How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.
b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.
I have several competitors balance sheets (around 15), they all have the same structure, what i will like to do is summarize all this data into a pivot table to have a grand total but also be able to filter the data by single competitor.
I have tried to do multiple consolidation ranges, power pivot, pivot but i was unsuccessful, maybe i arranged the data wrong or im not using the right solution. im using excel 2013
I need a macro that will take hours by day (columns) by service (rows) per client (sheet) and summarize the data into one database of rows containing client, service, date, and hours. The "Summary of Charges" should only include service hours > zero. I am attaching a sample file. I have little to no experience with vba so I don't even know where to begin. I can copy code.
View 4 Replies View RelatedI need to use to prepare for analysis a large set of individual participants' Excel data files collected using a psychological reaction-time experiment (a modified Stroop task). This experiment was created using a program called Superlab by Cedrus, so I posted an inquiry at the Superlab forum [url]. Their tech support suggested I might need to use macros in Excel but couldn't offer further assistance. I'll try to briefly describe (a) our Stroop task, (b) the operations I'm trying to carry out, and (c) the format of the individual Excel files,
Our Stroop task: The participant gets some instructions and practice trials, then a series of stimulus words are presented one word at a time in the center of a computer monitor in one of several colors. Word presentation order is randomized for each participant. The participant must press a key corresponding to the color of each word as quickly as possible. For each keypress (including the spacebar, pressed on instructions screens), the reaction time (in msec) is recorded in their Excel spreadsheet under the 'Reaction Time' variable. The 'Error Code' variable indicates whether the participant pressed the correct (C) or incorrect (E) color key in each trial. There are 8 types of words for a total of 48 trials. (Actually, 4 sets of emotion words and 4 sets of control-animal words, each set composed of 6 words each. The task basically measures how much peoples’ attention is captured by emotionally relevant cues in the environment.)
For each participant's data, I need an efficient way (macros?) to:
(1) clean it [i.e. discard or ignore the instruction & practice trials as well as trials where the participant was too impulsive (reaction time < 100 msec), too distracted (rt > 3000 msec), or gave the wrong color response (Error Code= E)]
(2) determine whether enough valid trials remain for further analysis (>39 trials), and if so:
(3) calculate a mean reaction time score for each of the 8 word types [where denominator for each word type is based on # remaining (non-discarded) trials for that word type]
(4) merge each participant's summarized data as single rows in a common spreadsheet (for importation and further analyses in SPSS), like this:
---------------------------
ID#...... MeanRTW1.......MeanRTW2...... (etc. for word types 3-8)
101.......1056................2013...............
102.......2148................2594...............
103...
---------------------------
Excel File Format: I've attached a sample file, and here's a simplified overview of a spreadsheet (irrelevant columns and some rows removed). First the ID number is recorded (e.g., 393E), then some unnecessary junk (exp name & date, intro, practice, instructions), then data for the 48 actual trials of interest:
-------------------------------------------
........A...............B..................C.................D............
1 393E
2 stroop.xpt
3 Wed Jan 1, 12:33:00 2008
4
5
6 Trial Name.....Trial No..... Error Code.....ReactionTime
7 introduction..... 1................C............561243
8 practice.......... 2................C............... 2062
9 red-worried........14.............C................1001 (*the first practice trial)
10 (9 more practice trial rows, deleted here)
19 instructions........3..............C................5000
20 red-chicken......19..............E............... 1205
21 blue-tense.......32...............C.................782
* (46 more actual trial rows, deleted here)
--------------------------------------------------
To clarify:
-The variable 'Trial Name' indicates nominally what stimulus was presented for each trial.
-The variable 'Trial No.', indicates the unique numerical marker for each stimulus word. (It probably should have been labeled 'Word Type'. It doesn't reflect the order of stimulus presentation. E.g., the word "tense" is always 'Trial No.' 32 for all participants, regardless of when it presents.)
-A subset of actual trial words (e.g., worried) were also used for the 10 practice trials. Thus, the 10 practice trials must first somehow be discarded or flagged to be ignored before calculating average reaction times for the remaining 48 actual trials.