Summarize, Report & Print Data

Aug 28, 2006

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)...............

View 5 Replies


Loop Through Specific Ranges From All Sheets To Summarize Data In Report Sheet

Jul 1, 2012

i attached link to sample and its contains employee sheets and Report sheet ,and in Report sheet there is comment


View 7 Replies View Related

Report & Summarize Counts

Sep 25, 2007

Refer to the attached sample workbook. I need to track daily tasks of our staff and then indicate these tasks on the "Activity Report" sheet.

Each task will have a code and I will indicate; by menas of the code, which activity was done by inserting that code in the time slot provided.

Once each day is completed; I need to tally up the amount of each activity that took place and it will be diplayed in the "Specific Activity Summary" section of the sheet.

Also; once the tally for each activity is completed, the amount of time spent on that activity will be indicated on the "Daily Activities Summary" sheet.

How do I go about linking the specific cells to be able to create this "tally" and "summary."

View 9 Replies View Related

Print 1 Report Page For Every Row.

Jan 16, 2010

how to print one report page for every row on a sheet. So if I have 300 rows containing personal info like name phone email I would like to print the data out in a separate formated report for each person.

View 3 Replies View Related

Print A Report Based On Cell Value

May 25, 2009

I'm looking for the same thing, this great macro by Leith Ross looks for the word "buy" in kolom A starting at A2. Now this all works fine until i change the value of the colum into a if statement : =IF(C2>0;"buy";"NR"). See the attachment on what i mean. If i run the macro now i get error 91 : objectvariable or blockvariable With is not set.

I can work around it, if i hardcode the A2 with "buy" and then A3 etc with the if statement i works for some strange reason. But there must be a smarter way of doing it, that's why i came here .

View 2 Replies View Related

Macro - Print N Rows Into Report?

Mar 14, 2014

I have n rows on a tab and I have counted them using Offset and Counta and put that count on B1.

I now need to make a little macro that print from row n to row number in B1. I've seen macros using things like "For i" "Next i", etc... but I do not know how to set the macro to start at a cell with a number n and repeat till the number in B1.

View 9 Replies View Related

Print Named Ranges .. Report Manager

Feb 8, 2008

I am working on building a better report manager using named ranges

1.) Loop through the workbook and find all named ranges
Output the named ranges to a worksheet

2.) Read the values from the worksheet and print each one

Seem simple enough and is really just first step
Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed

But I'm getting ahead of myself.
First is to print a named range

My code is below
Error msg:

Run Time Error 91
Object variable or With Block Variable Not Set

Debug points here:

Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value

1. Loop through all named ranges and output to worksheet. This part works fine:

Option Explicit

Sub GetNamedRange()
Dim nName As Name
Dim wb As Workbook
Dim wsPrnt As Worksheet
Dim Row As Long

With Application
. ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With

View 6 Replies View Related

Copy Data From Weekly Report To Monthly Report

Jul 2, 2008

How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.

View 4 Replies View Related

Summarize The Data By VBA

Jul 5, 2007

Date ModelStationLot_SizeSampleReject


How can I quick summariz these data by VBA ? as below result.

Date ModelStationLot_SizeSampleReject


This is just my sample data. The real data is more complicate than this. I want someone suggest me about VBA code.

View 9 Replies View Related

Sumif - Summarize Data

Mar 22, 2007

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.

View 9 Replies View Related

Summarize A Table Of Data

Jun 20, 2006

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 Related

Summarize Data In Another Layout

Aug 25, 2006

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.

View 9 Replies View Related

Summarize Data From All Worksheets

Oct 4, 2007

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 Replies View Related

Summarize Data By Criteria

Apr 11, 2008

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 Related

To Summarize On List Of Data - Can't Pivot

Aug 21, 2007

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.

View 14 Replies View Related

Summarize Customer Order Data

Jul 3, 2009

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

View 9 Replies View Related

Summarize Data Spread Across Workheets

Oct 23, 2006

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?

View 9 Replies View Related

Summarize Data In Pivot Table

Dec 30, 2006

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..

View 4 Replies View Related

Copy Data Between Sheets & Summarize

Sep 8, 2007

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?

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 ...

View 8 Replies View Related

Summarize Quarterly Data In The Summary Table?

Aug 7, 2014

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 Related

Summarize Daily Data Into Weekly Average

Jan 17, 2009

I 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?

View 5 Replies View Related

Summarize Data Based On Multiple Criteria

Jan 6, 2010

I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:


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.

View 3 Replies View Related

Data Validation - Summarize Three Lists In One Table

Jan 4, 2014

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 Related

Index Match Vs D Function To Summarize Data

Oct 24, 2008

I 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.

View 10 Replies View Related

VBA Or Formula To Summarize Data Horizontally Into A Table?

Feb 11, 2013

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.

View 1 Replies View Related

How To Summarize 52 Weeks Worth Of Data Into One Workbook

May 15, 2013

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.

View 6 Replies View Related

Compare Data-set With List & Summarize Matches

Jul 16, 2009

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.

View 8 Replies View Related

Summarize Data From Details Workings By Given Single Input

Sep 6, 2013

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.

View 3 Replies View Related

Macro To Summarize Data From Multiple Worksheets With Different Ranges?

Jun 25, 2014

I 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 Related

Pivot Table From Database To Summarize Data In Columns

Sep 9, 2008

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.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved