Every month I need to consolidate the individual report in a single sheet for that I was doing with copy paste options but I need this to happen through macro or some other short way.
View 5 RepliesWe have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
View 3 Replies View RelatedI 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.
I have 2 sheets which validates data using vlookup.
Sheet1
Table 1ABResult1121232313
=VLOOKUP(A7,B:B,1,FALSE) is the formula in result1 column.
Sheet 2
Table 2DEResult21020102040203010#N/A
=VLOOKUP(F7,G:G,1,FALSE) is the formula in result2 column.
The basic idea of these vlookup is to check whether the data present in A or D column is present in B or E. If the data is there then it will be retrieved in "Result1" or "Result2" columns. If not then the result would return the value "#N/A".
What im trying to do is to copy the resultant data(from Result1, Result2) other than "#N/A" from individual sheets to a final consolidated sheet.
I got a workbook with one active sheet. There are 6 colums and 55 rows. I want to creat a macro or formula to automatically print each individual row on a single sheet of paper. i will only need the line with a specific value printed i.e only print value more then 5
View 9 Replies View RelatedI have a supplier list which i access all the time. To view individual supplier details i use find function using supplier code and then scroll through the sheet to view all the details and it is quite hectic. Is there any way i can modify the sheet so that every time i search for a supplier , only that supplier details can be shown in a different sheet or notification which i can also print if i want.
View 2 Replies View RelatedI have a report that has the option to download the data to an excel file, but am having trouble with the last portion of my macro to re-sort the data into something useful. Basically, I have all of my data in a single line, except for some "oddball" records that have an extra number in the row below (which I need). I did a quick macro record of what needs to happen with each one of those "oddbal" records, but I'm completely baffled as to how to get this to repeat every time the macro "sees" something in that second line. I've tried modifying the saved macro in the attached Excel file the following code (the If loop), but I'm getting syntax error that I cannot seem to resolve.
Sub Macro5()
Cells.Select
Selection. AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="="
If range "J" Selection.AutoFilter Field:=1, Criteria1:="<>"
Range.currentrow.offset(0,9).Select
Selection.Copy
Range.offset(-1,1).Select
ActiveSheet.Paste
End If
End Sub
how to consolidate all data from all worksheets into a single worksheet(master sheet)?
Untitled.png
In the attachment, data for worksheet 1 will be added to the masterlist and worksheet 2 will follow.
I imagine my goal could be achieved via some scripting code, but, alas, I don't do vbs. Here's the situation:
I have a spreadsheet generated by another office which lists individuals' names and information about participation in various programs. Each line lists name, address, program title and amount. The next line may be the same name, address with a different program and amount. Each person may have 4-5 entries. I want to make single entries for each individual, with colums showing the programs and amounts.
Current:
ID Name Address Program Amount
1 Bob home A 25
1 Bob home B 37
2 Dave home A 22
2 Dave home B 10
2 Dave home C 21
Need:
ID Name Address Program A Program B Program C
1 Bob home 25 37 0
2 Dave home 22 10 21
Hopefully this makes it a bit more clear. The only way I know how to accomplish this is the "old fashioned" long hand approach of cut & paste. There are several thousand individuals, with (currently) tens of thousands of rows.
I have thousands and thousands of rows of data which look like the spreadsheet screen shot shown in this image:
ExcelConsolidateRaw.JPG
I want to consolidate them so that they look like the spreadsheet screen shot shown in this image:
ExcelConsolidateDesired.JPG
How to insert it into a module in the VBA editor.
I have seen many threads on how to delete rows with no data in them (empty rows).. but I have a spreadsheet that has large "chunks" of column data separated by random empty cells. (It is like this from earlier manipulation of summing similar cells...)
Some columns do not have any empty cells and some have a many empty cells. I only want to shift the cell directly beneath it up, and only concentrate on a single column at a time.
So my main questions is:
How do you delete single cells, in a single column, and shift the data below up?
I want to make a single graph from two data sets that have individual dates.
help!.xlsx
E.g.
Data set 1
1. January 2010 2. january 2010 ... 10. december 2010 etc
10 15 ... 10 etc
Data set 2
1. January 2010 ... 1. January 2015 etc
200 ... 200 etc
my macro comes up with an error on the Consolidate line that says 'Cannot add duplicate source reference'. I have a workbook with a variable number of tabs and I want to consolidate-sum (not copy and paste) the data from the various sheets onto a summary sheet within the same workbook. I'm at the point where it all works fine until it hits Consolidate.
Option Explicit
Sub ConsolidateExport()
'
' ConsolidateExport Macro
[Code]....
I have keycodes in a single column that represent various product attributes.
For instance:
1304DP_CVDA2_CC_
1304EP_CVD_CC_
1400BR_CV_O_
1610__RA_
Everything before the first underscore can be ignored, the keycodes are after the underscore. If there are two underscores (__) there are no keycodes for that product. The next underscore after the keycodes represents product categories and can be ignored. Is it possible to break individual keycodes into their own columns?
For example, 1304DP_CVDA2_CC_ would become:
1304DP_CVDA2_CC | C | V | D | A2
I have individual letter keycodes for the entire alphabet range A - Z, and one oddball A2.
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.
View 4 Replies View RelatedI am using Excel 2010.
At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.
17,43,61,63
17
43
61
63
23,29,53,57,77,79
23
29
53
57
77
79
17,29,63,69,71,75,79
17
29
63
69
71
75
79
11,43
11
43
57
57
I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.
I am using Excel 2007 and need some help. If you could please download and open the attached excel sheet then i guess you will be able to understand my problem easily. I am also not sure if what i am asking is really possible or not in Excel (would prefer without any VBA coding but if there's no other way then VBA coding would also do).
What i need is that based on the data from 'data' sheet i want to dynamically fill the ' Date' column in 'CatA' sheet. If you check the 'Expected Result' sheet where i have manually filled the dates and automatically calculating the 'Amount' but i was curious to know if somehow i could dynamically fill the values in 'Date' column based on month name in header?
I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?
In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.
I would like to calculate the sum of investments based on their expiry date and have the totals per month (and year). I have a table that looks like:
TermInterest rateMaturityBalance
24 Months7.12%11 November 200740,000.00
12 Months7.74%13 November 200750,000.00
24 Months7.05%10 January 200853,889.12
12 Months7.85%11 January 2008120,000.00
12 Months8.02%22 March 200817,000.00
36 Months6.68%30 June 200832,000.00
I'd like to have something like:
Nov 07 90,000.00
Dec 07 0.00
Jan 08 173889.12
and so on...
Admittedly I am an Excel novice, so excuse me if my question is dumb and has a simple answer (actually I hope it has :-) but I have tried to find a solution by searching forums, my books, online help, I tried my luck with sumif and SUMPRODUCT functions, even used the conditional sum wizard, but I can't get it right
Not sure if this is possible. From the attached spreadsheet (reduced to only 2 worksheets), i'm trying to create a formula that will find how much an individual trader made for the month (worksheets 1-31 being a month). The problem is a trader will not be at the same stall everyday and may pay by either cash or cheque.
View 3 Replies View RelatedI have a master spreadsheet that list several columns about employees(name, date, event name, etc.). Then I have an indivdual sheet for each employee. I am trying to import the column information for each employee onto their individual sheet based on their name. It is possible to import the individuals data from the master sheet to the individual sheet based on the employee name?
View 9 Replies View RelatedI have been asked to create a report that rank top sales agent for the month with the following variables:
Total # of sales-35%
Total $ of sales-45%
Number of calls made-20%
Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
2) the total workdays days for that period.
with an HLOOKUP issue which seems to work with every month Valeu except Jan. The attached workbook has 2 named ranges ACTUALS and BUDGET. In D20 I have < =HLOOKUP(TEXT($E$1,"mmm"),Actuals,2,0) > which provides the correct information for every value I put in E1 except a January date which always returns a #NA error.
In E20 I have < =HLOOKUP(TEXT($E$1,"mmm"),Budget,2,0) > which does not return the #NA error. Both named ranges are formatted the same and both are refering to the same lookup value.
Can I modify this forumla to total for the whole month rather than just a single date:
Here is the formula:
=COUNTIF(INDEX(D:D,10):INDEX(D:D,500),A4)
So if A4 is "01/04/09" I would like to count for the whole of April, not just the 01/04/09.
I have a file that will have a maximum of about 25 tabs in it. They are all the same format - Column A have account codes, Column B have the account name and Column C through to Column AD have figures. Column B has a drop down menu, so not all tabs will have the same account code on the same row.
I thought that I would create another spreadsheet in the file and call it a master sheet with the same formats as all the other tabs but this master file will have all of the account codes available and will not have a drop down menu in column B. What I wanted to do was consolidate all of the 25 tabs in the master sheet.
Example Worksheets
TAB 1
Acc Code Desc Amount
1________a___10
2________b___10
TAB 2
Acc Code Desc Amount
1________a____10
2________b____10...........
I have 2 pivot table in 2 different sheet and i want to consolidate the data in one sheet.
Pivot table:1(order processed) as below
date
MR X
MR Y
MR Z
MR S
[Code].....
I need the data as below in separate summary sheet. 2 column in each employee(1 is processed and completed), i dont have option here to create 2columns here hence i put "/" in between.
date
MR X
processed/Completed
MR Y
Processed/completed
[Code].....
I'am have one data file with so many sheets those need to edit and bring them to final summary sheet.
I dont know how to move between sheets and edit them automatically.
Editing will need to add the brand name next to the variance name and the sheet name in one new column. And bringing those rows with completed data only to All sheet from those sheets in workbook.
Trying to consolidate and Merge Data on a Separate Sheet
View 4 Replies View RelatedI Need to write a macro for the following example.
In sheet 1.
It has data as below
HTML Code:
Sl.no Name Task week hoursworked
1 SAM Teaching 1 2
2SAM Teaching 1 2
3 SAM Teaching 1 3
4 SAM Teaching 1 1
5 SAM Teaching 1 5
I want to consolidate the data as like below and to paste it in sheet2.
HTML Code:
Sl.no Name Task week hoursworked
1 SAM Teaching 1 13
If the Name, Task and Week columns are same then macro should do sum the hours and make it into a single line as mentioned above.