Here is the attached Excel file and the following is the desired output of the macro:
1.) List the data (Names) of the Columns D (Input), F (Analyze), and H (Output) in Sheet1 to Column A (Name of Person) in Sheet2. There should be no repetition of two names.
2.) Count the number of entries of each person in the Column D (Input) in Sheet1 appears per month (basis is the Input Date column E) and record into the corresponding month in Sheet2 under the Input Header.
3.) Add the total of the 12 months in the YTD column under the Input Header.
4.) Repeat steps #2-3 for the Column F (Analyze) and Column H (Output) of Sheet1 with the results recorded in their corresponding headers in Sheet2.
5.) Note: The data in Sheet1 is a running data and continually adds up as the current year goes by. If there is a way the macro could take that into account it would be much better.
I have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
I was asked at work to create a report that takes raw data from a questionnaire and compiles it in an easy-to-read report. (Little do they know that this is how I'm such a wiz at excel ;-) )
It seems pretty simple, but I can't seem to figure it out! On the attached workbook, the first spreadsheet,"SUMMARY", is the jist of what the report should look like - the average score for each question by floor. On the next spreadsheet, "RAW DATA", is a sample of what the raw data looks like - just a list of each room number and the score it received by question. the first 2 digits of the room number signify the floor (ex. 201=2nd floor, 1215=12th floor, etc.).
My major issue was with getting the score for each floor without having to manipulate the raw data - this report will eventually become a shell that can be used with new data every month. Is there a way to make a range equal a number (ie. 200-299=2)?
First sheet is comprised of 12 grouped cells. Each group consist of 7 rows and 6 columns the above mentioned groups are bwoke down with 6 lines of data and 7th row is totals of the 6 boxes.
Each gtoup will have totals, even if they run over to next group The first column is a task number, which will be the main component. A task number can be used multiple times and can run over to next group of cells
Here is an example of above mentioned sheet task# Start time end time Description 999.......15:00.......15:05.........Open Mail 999........16:05.......16:55........sort claims
A new task number will move to the next group of cells 748........15:05.......16:05.........23 inches
2nd sheet will be a time tracking sheet. Here is the info from this sheet 999......15:00.....15:05.....open Mail 748.......15:05.....16:05......23 inches 999........16:05.....16:55......sort claims
The 2 sheets look alike but on first mentioned sheet info is broken down by task number
I'm stumped on what I know is a pretty basic problem. Maybe i'm just trying to over think it.
I have a table of sales data...One field is the date it was sold, one field is the amount it sold for. The date field isn't in order and it contains dates over the past 12 months. I need a way to total the amount of sales in each month and not through a pivot table. I am able to count how many entries there are, but I can't find a easy way to do a count of how much was sold in each month.
I have a spreadsheet with 12 tabs, 1 for each month in the year. On each tab I have employee names (column A), followed by the premium they pay for their health insurance (column B). I'm attemting to create a list of each employee and how much they paid for helath insurance over the year. Since employees come and leave thorughout the year, the lists aren't the same on each tab. However, I was able to create a master list of all possible employees and placed this list into a new tab.
I'm vaguely farmiliar with the SUMIF function and I believe that this would be the best approach, or some possibly sort of vlookup.
I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.
An example source file is found here: [URL]...
As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.
I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.
I've got a tab with 32K+ lines of data in six columns. I need to be able to summarize it in a table based on two criteria (e.g. Region, Line of Business). I am currently using 16 sumproduct formulas (=sumproduct((type=a5)*(reg=a4)*revenue)) where type is the line of business and reg is the region column, and 8 vlookups per region. Each tab will have about 8 regions and I'll have 4 tabs. As you can tell, this spreadsheet will quickly get out of hand in terms of calculating speed. I can't really use a pivot table because of: 1) formatting constraints 2) the vlookups refer to a separate tab than the main data dump. What alternatives do I have other than array formulas to get the data in a nice little summary form? The formulas/presentation aren't very complicated, but I have yet to come up with a good alternative to the above.
My company would receive RFQs from potential customers inquiring the availability of different products, and we are trying to summarise these requests to find a trend.
Each product has several attributes, such as descriptions, keywords, manufacturer's code, etc. The RFQs are all different, with each request providing some attributes while missing others. The only reliably uniform attribute is the product number.
I am trying to build a database with the maximum amount of details on each requested product, therefore consolidating the different requests. I already put the source data on the same sheet and sorted the product numbers. I also know how many instances there are for each product number. In other words, my data look like this:
Product number (sorted) Repeat count Attribute 1 Attribute 2
111a 2 aaaa
Note: 1. The product numbers and repeat count are reliable 2. All the attributes are valid, i.e. as long as one can fill a blank I would take it 3. If none of the instances provide a certain attribute, it is acceptable for it to be left blank
And I am trying to turn it into this:
Product number (sorted) Repeat count Attribute 1 Attribute 2
111a 2 aaaa bbbb
123456 1 abcde
100x 3 123456 wxyz
I already spent a whole day trying to do it with MATCH, OFFSET, VLOOKUP etc to no avail. It seems I would need some kind of VBS with loop and array functionalities that are beyond me.
I have a spreadsheet with column B being the date when a staff joined the company; column C being the date when the staff left the company. If I want to set a formular in column D to calculate just the number of month had a staff been working in the company, how should I set?
e.g any day joined in Aug 05 and left in Sep 05 will be counted as 1 month
I have items arriving on certain dates (the dates are listed in column N)
In the cell to the right of each month, I want to have a formula that will count the dated cells in column N that fall under each month.
i.e. In cell B5, next to January, I want to display the total number of cells that contain a date in January (ultimately giving me the number of items that arrive in January) the number would read '5' because there are 5 dates in January that are in the list.
I would like to count how many Mondays, etc., that shh worked.
DateDayDVMClinic 11/01/06WedshhCal Vet Neuro 11/16/06ThushhCirby Ridge 11/22/06WedshhHardin Animal 11/27/06MonshhCrossroads-DS 11/24/06FrishhBear River 11/21/06TueshhLaguna Creek 11/22/06WedshhRoseville Vet 11/19/06SunshhMissouri Flat Pet 11/20/06MonshhCrossroads-Folsom 11/20/06MonshhSierra Vet-Stockton 11/20/06MonshhAnimal Clinic-Benicia 11/22/06WedshhBlue Ravine 11/24/06FrishhCat Clinic-Folsom 11/18/06SatshhAmerican River 11/21/06TueshhIndian Creek Vet 11/28/06TueshhSlate Creek Animal
Some dates will appear twice, because that will be relative to different clinics.
How to make information gathering easier. So I have a spreadsheet with information in one tab and Graphs & tables in another. I am trying to count how many times a word appears in my last column (the word is "HM - GM") but I only want to count how many times that appears in the month of Jul (The July column is my first column, it shows as "Jul-13" but when you click on the column it appears as 01/07/2013). I have tried a few =COUNTIFS formulas I found around the site, but none seem to work for me. I am trying to get the counted information into a table so it will show how many 'HM - GM' were in Jul-13.
I am trying to create a macro (pretty new) to take a sheet of data and out put the average of each column by date. The data will be filled every month and the numbers i need should be one for the 1st, the 2nd and so on for each day.
Here is a sample of the data in the sheet, i would attach a spreadsheet but cant seem to find out how.
TimeStampUTC Air Volts Dry Volts Dirt Dirt Fraction Temp
Please see the attached xls file so see what I am referring to.
I have shipments that are going to different destinations (rotterdam, austria, london, etc.)
I would like to count the arrival dates in column H that fall under each week's span, but ONLY IF its corresponding value in column F is 'rotterdam'.
Column C contains the ideal numbers that I would like column B's formula to return. I plan on doing this for the entire year, but if someone can some up with a formula, I might be able to modify it for the rest.
Note: This is only an example spreadsheet, I am going to be referencing an external file with much more information on it.
I am trying to create a graph that is conditional on two different columns. The first column is a date column, the second column has various categories. I want to show how many times each category appears per month. This database is continually added to so I wanted the formula to reflect the entire column range.
For example, let say I have 5 categories (Grapes, Apples, Peach, Pear, Banana). Column A would show a date (in a M/D/Y format) and Column B would list the fruit type. I want to show how many Grapes were input in January, February, March, etc. and then move on to show how many apples in each month, and so on.
In cell A2 on Sheet 1 = January. On sheet 2 in cell A2 I need it to = February, On sheet 3 in cell A2 I need it to = March, On sheet 4 in cell A2 I need it to = April, etc.... How can I do this with a regular text formula, not VBA coding.
I have a basic bookkeeping workbook with multiple sheets each representing a differenct account. In each sheet, an entry is entered on a row with main columns being date, credit (where a positive amount goes), debit (negative amount goes), and then a series of columns representing the type of credit or debit where a simple "x" is placed.
This style has worked well in the past becuase we merely just sort by column and the "x"s clump all credit or debit types together and we just sum them at years end.
However, we are now trying to have the numbers report to a main sheet that will summarize them monthly; basically where the first column is all the different types of credits and debits and the next 12 columns are the months.
We know how to refference the other sheet to get the data, but where it gets complicated is summarizing ALL the sheets together since they all have varying number of entires per month.
Is there some sort of way I can write a formula (or series of formualas) that will allow it to collect all the data from one sheet by only looking at the month column, and add that up, and then do the same for each sheet?
I would usually do this in a pivot table (which works fine) but I need something else as the person I am passing this on to will not know how to change the range and this 1 report is a part of a larger report which should just be as simple as: Data download >>> calculations run automatically >>> print
I need to sum up various bits of information. The attached file is the example. The DATA tab holds the information and the REPORT tab shows the column headers that I am after. These columns are as below:Contractor Name: The formula should look through the DATA tab and bring back a contractor nameApproved Quotes: Then taking the name from the above field, the formula should add up column T where column AP = YesApproved WO Count: This one should just add up how many rows the above total is made of.Rejected Quote: Then taking the name from the contractor field, the formula should count add up column T where column AP = NoRejected WO Count: This one should just add up how many rows the above total is made of.
The PIVOT EXAMPLE tab holds the example with the red highlight being excluded.
In the first sheet (EQ MOVES) I have: Column A contains a list of some 300 different pieces of equipment. Column D is where the foremen enter the job number (4 digits) and job name of where the foreman wants the equipment to go the next day.
In the second sheet (PERSONNEL MOVES) I have: Column A contains all employee names. Column D is where the foremen enter the job number and job name of where the foremen want the employees to go the next day.
In the third sheet (SUMMARY) I have: A box in A1 where I type in a job number (4 digits) but no job name. A2 says "EQUIPMENT and B2 says "PERSONNEL". A3 and below should list all equipment to go to that job listed in A1. B3 and below should list all personnel to go to that job number.