Grouping By Year ...

Feb 17, 2009

Is it possible to grp data in an excel sprdsheet by year or month and also is it possible once that is done to have an option of totaling each period?

On a separate point, but similar:

i have a spreadsheet in one of the columns i have a unique reference eg opal.... at the beggining with some other digits eg opalmimi, or opalniuj.

so i have like 20 or thirty rows (maybe more) of data .

What i would like to do is to sort by the column begining with the opal wildcard and grp and subtotal each wildcard grp

so my sprdsheet looks like this:

Date Desc (where opal values are entered) Amount

View 9 Replies


ADVERTISEMENT

Excel 2007 :: Grouping By Day And Month But Not Year

Nov 4, 2013

I want to count the number of adults and juveniles by 5 day intervals (Augest 1st-5th, 6th-10th, ect). I am currently using =COUNTIFS($A:$A,1,$F:$F,$A27) and manualy labeling the intervals (Aug 1-5 = g, 6-10 = h). I would like to know if there is a easier or faster way to do this. Some of my sheets are 10,000+ entries.

I am also a basic user of Excel so I am not famillar with a lot of the functions.

I'm using Excel 2007

This is a small portion of my data. Age code: 1= Adult, 2= Juvenile, the letters in F are my lables.

Excel 2007ABCDEF1AGE_CODEBANDING_DATEBANDING_DAYBANDING_MONTHBANDING_YEAR2110/31/196331101963y3110/31/196131101961y4111/1/19611111961y5111/1/19651111965y6111/3/19603111960y7111/5/19645111964z8111/5/19645111964z9111/5/19655111965z10111/5/19655111965z11111/6/19626111962z12111/8/19608111960z13111/8/19608111960z14111/11/196011111960aa15211/15/198115111981ab16111/18/201218112012ab17111/19/196419111964ac18111/21/196221111962ac19111/21/196521111965ac20211/24/200224112002ad21111/28/196128111961ad22112/2/19622121962ae23112/3/19613121961aeSheet1

View 8 Replies View Related

Message To Apear It The User Selects A Year AND Month Less Than The Current Year

Sep 25, 2006

I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.

'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen

ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here

View 3 Replies View Related

Formula To Compare Previous Year To Current Year

Dec 8, 2013

Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.

Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510

Sample file is attached : Comparison_Years.2011.xls‎

View 4 Replies View Related

Change Year But Keep Month The Same Based On Current Year

Mar 8, 2014

I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.

The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.

Attached is an example : Budget Example.xlsx‎

View 4 Replies View Related

Date And Month From A Column And Year Should Take Current Year

May 14, 2009

I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)

View 3 Replies View Related

How To VBA Activecell Value Year Equal To Current Year

Dec 13, 2011

I am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.

View 1 Replies View Related

Look Up The Date To See Which Year It Falls In And Return The Year

Oct 13, 2008

i have the following table of information

Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93

and a list of dates i need to look up the date to see which year it falls in and return the year

View 9 Replies View Related

Convert Date From Any Year To Current Year

Feb 27, 2009

DATE function won't return TODAY()'s year in the "year" slot.

Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?

View 9 Replies View Related

Line Chart With Year On Year Comparison

Sep 29, 2006

I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?

View 9 Replies View Related

Year Converted Into Decimal Year

Oct 21, 2009

1. I need to convert a year into a decimal year ie. 1830 into decimal year (I don't have a month, just year)

2 Year/month into decimal year/month

I just not sure what to do, is the year stored as a number/text/date. What should it even look like? Does 1830 display as 1830.00 using excel.

View 9 Replies View Related

Add Value From Last Year To Total On This Year In PivotTable

Oct 22, 2007

I'd like to know a formula which can calculate the moving annual total, that is the sum of the last 4 quarters. Now every time the sales from a quarter is known, i have to recalculate the MAT mannually.

View 2 Replies View Related

SUMIF Month & Year: Find Total Cost By Month Only For Year 2009

Dec 17, 2009

In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?

View 2 Replies View Related

Change Total Formulas For All Tables At Once To Show Either Year-to- Date Or Total Year

Oct 15, 2007

I have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.

For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.

View 9 Replies View Related

How To Get Only Month Year If There Is DATE - MONTH - YEAR In A Cell

Aug 20, 2013

How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?

A1: 27-July-2012

Answer D1:JULY' 2012

Pl note "' " is suffixed after JULY (the month).

View 4 Replies View Related

If / Then In A Macro - Changing 2 Digit Year Into 4 Digit Year

Jul 3, 2014

I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").

I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).

The statement I had was:
=if(A1>=50,1900+A1,2000+A1)

I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.

It seems like it should be pretty simple, but I don't know how to word it in a macro.

Starting Column Example:
12

13

14

99

11

[Code] ......

For some reason I can't get rid of the borders...

View 6 Replies View Related

Convert Date To Year/week Of Year/day Of Week

Apr 6, 2007

Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)

View 9 Replies View Related

Grouping Together

Feb 28, 2009

Sheet1 presents my "achievement".

Sheet2 shows what I'm after.

Can this be done WITHOUT any help columns/tables - just by using worksheet formulas?

View 6 Replies View Related

Sum Function In Grouping?

Jun 30, 2014

How do I sum only the total in various groups? I can highlight the cells to get the total but when using the sum function I get the hidden rows too.

View 5 Replies View Related

Grouping The Tabs..

Oct 13, 2008

Is there a way to group tabs? For instance, I have 3 tabs that go with one section and 3 with another and so forth. Is there a way to group them? Also, can you change their color?

View 3 Replies View Related

Grouping Duplicates...

Feb 26, 2009

I have a table in excel that contains many rows, each row being a product. Each row has a product ID, and should be unique, but there are multiple instances of products in the table, some that are duplicate and some that contain different info (product notes, description, etc).

What I would like to do is group the products by product ID, so that I can show the different occurances of the product within each product ID, so that we can weed out the unique values within the duplicate products by ID. Is there any easy way to do that?

excelexample.jpg

View 4 Replies View Related

Grouping In A Graph

Jun 16, 2009

I would like to create a graph where the numbers are grouped in such a way as all numbers between 0 and -1 are shown in one column, all numbers between -1.1 and -2 are shown in the next column, all numbers between -2.1 and 3 are shown in another column etc

The numbers are shown below

11/03/09
-2.5
-3.8
-3.9
-4
-4.1
-4.5
-4.6
-4.9
-5.2
-5.4
-5.4
-5.5.......

View 2 Replies View Related

Sum If And Grouping Dates

Oct 28, 2009

See attached spreadsheet. I would like to take all the departments (column a) and all the dates (column b) and group them. Then add up the sums for columns (e and f) respectively. See desired result in spreadsheet for entire range. How do I do that?
This is in excel 2003. So cant use sumifs.

View 4 Replies View Related

Grouping Within IF Statement?

Jan 31, 2013

Create an =IF() statement that divides the infants into four - lb. age groups (beginning at 10 lbs.) and assign a 1 to the lowest group, a 2 to the second, and so on.

10-14
14.1-18
18.1-22

for the three groups

View 1 Replies View Related

Grouping With Pictures

Mar 18, 2013

I have grouping setup in my excel spread sheet.

1. I added pictures(print screen-->mspaint-->cut existing part of the image-->paste into excel. In excel they are called "Pictures 01 thru Picture X" with the pull points all around.
2. I located them in specific cells of the grouped type with the (-) sign appears so the group of cells are in use
3. When I close the Group(+) of cells all the pictures are jumbled above the closed cells.

Question: Is there away to keep these pictures within those cells when closing the group, so they will not been see as well?

View 1 Replies View Related

SUM Results According To Grouping

Mar 14, 2014

I have a workbook that calculates the total sales per agent but looking a datasheet. At the bottom of the sheet I have 4 team managers who look after a group of agents. I need their totals to me calculated by summing each of the agents in the teams.

I have tried to create groups per team_manager using my team_ref_sheet. On this sheet I have Defined Names / Named Ranges which I thought would work for doing these calculations, but this does not seem to have worked.

View 5 Replies View Related

Grouping And Averaging

Dec 2, 2008

The organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).

One “reporting group” has many cost centres.

There are several reporting groups.

I need to report on average salary per grade / per reporting group.

I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.

In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.

View 11 Replies View Related

Grouping Like Items Together

Apr 16, 2009

I am working on a set of data and need to group items in the same style together.

Example: ...

View 10 Replies View Related

How To Sort In Grouping

Sep 8, 2013

I want to be able to sort product inventory information. The report generated for me shows on-hand inventory for each product in each warehouse (there are many rows in the spreadsheet per product - i.e. each product/warehouse combination has its own row).

I'd like to sort the table based on most total inventory per product, but maintain the products in their groupings. I know I could do a pivot table and sort it - the problem is, I want to able to see the warehouse distribution for each total. A pivot table would just give me the overall total without the breakdown per warehouse.

Here's an example:

Product #
Warehouse
Inventory

547
100
628

[Code] .......

From this, total inventory for product 547 is 694 units. For product 2091 it's 2,153 units. For product 1165 it's 286 units.
Therefore, I'd like the 2091 grouping to come first (all of it - including the rows showing 0 inventory), followed by the 547 grouping, followed by 1165 grouping.

View 1 Replies View Related

Automated Grouping Using VB

Oct 20, 2008

I have a large excel sheet that will take a lot of time grouping it manually, so I was wondering if there was some way somebody could help me out with creating a code in VB to automate this grouping for me. All I would need is something to read through the rows of one column and when it finds a blank row, keep scanning until the next blank row and then group the data between the two blank rows. I have attached a small portion of the excel sheet I am working with if you need to get a visual of what I am needing.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved