Report Of Data By Criteria
Jan 18, 2008
I have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want. Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet. My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item. Issues to consider:
-Line item 1 may not always be the top item. (because the user may not choose the top item)
-There will be spaces inbetween the line items since the user may not pick all of their chosen items in order.
-Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)
This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references
View 2 Replies
ADVERTISEMENT
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
Mar 26, 2009
Right to the point. I got two sheets in my file, one named "Order" and one named "Input". In the input sheet there is costs & revenues divided into several different divisions/activities. In column A I got the name of the cost/revenue and then the value for every period Jan-Dec in columns B-K. And the name of the activity is in column A under the cost/revenus for that activity, like a " SUM" row.
Now, in the the "order" sheet, you can select a specific activity from a rolllist and then I want that activity's revenues & cost to be shown. I've attached the file to make it easier for you guys to understand my needs. I think I need some sort of 2 criteria VLOOKUP, that is first search för the correct division, once this is found search for the different costs & revenues and the return a value. But I'm not so good at this so I can't get it to work. Maybe the INDEX function is good here but I don't get that at all. I tried Daves "2 criteria Vlookup" but all I get is #ERROR.
View 4 Replies
View Related
Jan 29, 2008
I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.
Column 1 = Dates
Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group)
Column 5 = indicates either Start or End of Task ('S' or 'E')
(each Task has 2 such listings - a Start [s] and an End [E])
columns 6 & 7 = Misc. & Notes (unimportant as identifiers)
So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).
Tasks List:
Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes
Jan 1 2008 | AA | def | XX | S | B-11 | notes
Jan 1 2008 | FF | xyz | ZZ | S | C-44 | notes
Jan 2 2008 | DD | def | YY | E | J-55 | notes
Jan 2 2008 | GG | abc | CC | S | C-22 | notes
Jan 2 2008 | BB | xyz | DD | S | M-33 | notes
Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
Jan 3 2008 | BB | def | ZZ | E | A-99 | notes
Jan 4 2008 | GG | abc | CC | E | C-22 | notes
Jan 5 2008 | AA | def | XX | E | B-11 | notes
Jan 6 2008 | BB | xyz | FF | S | J-55 | notes
Jan 6 2008 | DD | abc | AA | S | A-99 | notes.............
View 9 Replies
View Related
Feb 1, 2008
I am trying out a new spreadsheet to log my cycling. I have attached the example so far. I am going to have a form appear and fill it out. Upon clicking 'Add' button I want a macro that will search column B (a list of dates) and match the date the users filled out on the form with the spreadsheet and then paste the corresponding form data in that row.
How would I accomplish this? Does the formatting of the date field complicate this since on the spreadsheet I display "Saturday 1/08" and on the form the user enters in "01/08/2008"
View 9 Replies
View Related
May 31, 2014
I am trying to pull a value from a report based on two criteria. I want to get the most recent date that has a value in Col K
1
Col F
Col G
Col H
Col I
Col J
Col K
2
106
ET
Product A
14-May-2014
[Code] ........
I use
{=IF($K$2:$K$60000>0,(MAX(IF($F2:$F$60000=C2,$I$2:$I$60000))))}
On another sheet (I left the sheet names out of the formula to make it easier to read) with C2 as the matching Col F value and it will return the most recent date, May 19th, not the most recent date with a value in Col K, May 18th. The report itself is thousand of lines long and has a couple hundred unique values for Col F.
View 3 Replies
View Related
Jun 20, 2006
I have the following worksheet (see attached). What I would like to do with it to create a report is as follows:
1. Create separate reports for each estimator (KM, JW, WH)
2. Carry over the information in Columns I, A, B & H
3. Each report will filter column I to leave out COMPLETED and just report the number of days remaining is ascending order.
4. Flag data if the days remaining is <=5
5. Generate an email to send out notification of 5 days remaining
6. New report can be generated on any given day
View 9 Replies
View Related
Feb 28, 2008
I have a 5 column LIST that will have new rows added daily. Column A= Date (mm/dd/yy), B=id (s or u), C= ArriveTime (hh:mm), D=DepartTime (hh:mm). E is Delay time (D-C). Arrival and Departure times will vary from 5 AM - 9 PM but will always be on the same day. I would like a count of all s with delay of 0-6 min, 7-12 min, 13-18 min, 19-24 min and 25+ minutes. Also count all u with same delays. I should get 10 results (1 of each) using the following data. Would also like the mean, median, mode, min & max for all C (Arrival), D (Depart) and E (Delay) times.
1/1/08 s 08:00 08:01 1
1/1/08 s 08:00 08:07 7
1/1/08 s 08:00 08:13 13
1/1/08 s 08:00 08:19 19
1/1/08 s 08:00 08:25 25
1/1/08 u 08:30 08:31 1
1/1/08 u 08:30 08:37 7
1/1/08 u 08:30 08:43 13
1/1/08 u 08:30 08:49 19
1/1/08 u 08:30 08:55 25
View 2 Replies
View Related
Sep 13, 2013
So I am trying to rearrange some cells on a report based of some criteria using an IF formula and getting Blank cells. Here's what I have:
LastRow = Range("A" & Rows.Count).End(xlUp)
With Range("P2:P" & LastRow)
Formula = "=IF($F2=""/FEDERAL EXCISE TAX"",$I2,"""")"
.Value = .Value
[Code].....
So I just want it to identify whats in a cell and populate a related value if the text is present. Do I need to change formatting to find Tex or something along those lines?
View 2 Replies
View Related
Sep 21, 2006
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
View 9 Replies
View Related
Feb 16, 2014
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
my output needs to be partno, date due, quantity.
View 4 Replies
View Related
Mar 31, 2014
I have two tables (Table 1 and Table 2). Table 1 has names of all employees in my organization ('Name' column) and corresponding information in the second column ('Textinfo'). I need to create Table 3 from table 1, but only select those employees who are in my team, ie matching names in the Table 2. I also need to extract corresponding 'Textingo' column information. I tried vlookup but it did not work because if there more that one name in the table 1, vlookup confuses it (i think). Would you know what function or code to use?
Sample file is attached : excel question 1.xlsx
View 2 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jul 4, 2014
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
View 1 Replies
View Related
May 31, 2006
breeze.
Here is my situation:
X Y Z
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
A
B
C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
View 9 Replies
View Related
Jun 21, 2008
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
View 9 Replies
View Related
Jan 4, 2010
I have attached a spreadsheet that has the following tabs: powerhouse report, influent clarificatioin report, and data history. The PH report and Influent report are supposed to take data from the data history tab. which is named POWER. My problem is that the PH report does take data from the data history tab but the Influent report doesn't.
View 2 Replies
View Related
Feb 7, 2008
I have a workbook of about 30 worksheets which makes extensive use of Data Validation. Essentially, data entry cells is validated against values within some predefined ranges (there are 6 ranges in total).
I am preparing documentation for this workbook and would like to document the data validation being used. Is there a way to extract this information? Where/How is it stored?
View 9 Replies
View Related
Jul 16, 2008
I would like to transfer data from one tab to another to for a report.
First off I would like to take all the rows that have "O" in column B. But only if the year in coloumn Q or R is this year. (Q is order data so I show all new orders taken this year) (R is despatch date so I show all orders despatched this year)
There will be around 25 orders total
I would then like to add a total to the bottom of this set of data.
Then move 4 rows down and bring all the data that has a status "HP" in column B regardless of any other criteria.
There will be upto about 100 Hot prospects (HP's)
My data is held on tab "BRAND" and I would like to generate the report on "BRAND HP"
My data has a header row in row 1 and holds data from A:T
The data is of ever increasing length.
View 9 Replies
View Related
Aug 24, 2007
I want to create a report from an existing excel spreadsheet that will show the first row with column headings. I only want certain columns from the spreadsheet to show on the report (see attached). On the left (1st column), I want to show the data from each of the last 7 days. Then I want to show data for rolling last year to date, then for last rolling year to date, and finally for last rolling 30 days.
View 2 Replies
View Related
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
Jul 12, 2013
I have created a excel document. Would like to create report using the data.
create reports using excel data
View 3 Replies
View Related
Dec 13, 2013
I need to extract and format a large data base of county real estate tax parcels for a report. See attached. How can I combine these or is it possible.
View 2 Replies
View Related
May 11, 2009
I have made several stabs at extracting data from an inventory report, but keep hitting roadblocks and would appreciate some help in my 1st VBA project.
I have attached a spreadsheet with sample data - the data is in a fixed format, and I have overwritten unnecessary data, but all the data to be extracted is in Columns D, O, and P.
My approach was to go down column D testing for non-blank fields that do not begin with "Total", thus creating an array of item names. Then I would add the code that would go across adding Quantity and Value into the array.
View 7 Replies
View Related
Aug 8, 2012
trying to do anything other than the basics with Excel. I would like creating a macro to import data tables contained in a report that I receive in HTML format.
The Report looks like the following. I de-identified the data
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR
PAGE 1 01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED:
DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT
DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT
MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
[Code] ........
The column group represents a security profile for an application. I am auditing the access to that application. They either start with the '#' symbol or the '@' symbol.
I receive this report once a quarter. This being my first time. I had to manually cut and paste the data tables from the HTML report file into a spreadsheet.
This is what I would like to be able to do in excel using VBA. The report name is the same every time. 'FS.ASET.Users.htm'
1. Would like to create excel spreadsheet with a macro button and master worksheet that already has the headers.
Group Userid User Name Owner Create Date Special Operations
Auditor Revoked ADSP GRPACC UACC Authority
2. That looks in a network directory(will be the same everytime) for this htm file(will be same name every time)
3. Pull only rows that start with # or @. Place them into single worksheet with sorted by 'Group' ascending.
4. A stretch goal would be to have the macro create a different worksheet within the spreadsheet for each profile name, I.e.,@UFSGUAR, #ZCBSAU, and copy only those rows that start with that profile name into each subsequent worksheet.
View 5 Replies
View Related
Mar 28, 2007
First I copy and paste the text into Excel. All of the data is in column A because of varying lengths, which seems to work fine. I've already written VBA code (shown at the end of this posting) that reads the contents of each cell in column A, pulls out the key information (like the actual error code or invoice #), stores the fields in variables, switches to a separate tab that has a more user-friendly layout, locates the next empty cell in the appropriate column for the field, and puts the variable contents into the cell. I realize that the code currently has some variables that aren't being used and other basic issues, but that's not really what I'm after at this point.
The report itself includes billing errors, with each billing error separated by a series of asterisks. My current code works just fine for sections of the report like the first section in the sample below, where the billing error only contains a single invoice, error code, description, and action item. If the entire report was formatted in that manner, my task would be finished because it does those sections perfectly well.
But...if you notice in the sample report below, the third billing error contains more than one invoice, error code, description, and action item. Obviously, using my current code, when the macro encounters that section, it continues to function, but the rows do not align properly in the separate worksheet because there are extra invoices, error codes, descriptions, and action items. I just cannot figure out a way to have the macro keep the information aligned. In other words, in the third section of the sample report below, the billing error contains more than one invoice and item.
One approach that I've thought of is to try to count the number of rows between the cell containing the previous set of asterisks and the cell containing the next set of asterisks, because it's the information between each set of asterisks that I need to work with. But I haven't found a way to do that and don't even know if that's the direction I need to go or not. I'm pretty much lost at this point.
Here's a sample of the text report as it appears in column A (the fields are actually longer than this - I tried to simplify as much as I could for this posting): ....
View 9 Replies
View Related
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
View Related
Dec 4, 2006
im creating a spreadsheet at work that gathers project and FTE figures.
I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.
Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.
Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".
can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.
View 9 Replies
View Related
Aug 21, 2007
I'm trying to make an excel spreadsheet for a Kareoke business - what I want to do is have a database of CDs that they have in 1 sheet - this will include Song Name, Artist, CD Title, Track Number and CD Number - then I want the user to be able to go into the next sheet and type in the CD Number into a specified cell and thus will import all the information for that CD number into the required fields. I have attached a copy of what I sort of want it to look like. I don't really know the functions of Excel, but I'm quite computer savvy and should be able to work it out with instruction - please note, I've never used a "macro" and don't know what they are - so if I need to use a "macro" would you be able to give me a link to somewhere that explains what they are...and how to use them?
View 3 Replies
View Related
Dec 11, 2013
I have a huge Excel spreadsheet with various worksheets and data, I want to merge some of that data into a report using word template. How can I pull simple data like the company infomation into that word report from Excel? Do I need to know VBA to accomplish this?
View 1 Replies
View Related