I need to modify a pivot table that has dates as one of it's headings. Instead of listing each individual date, I need to group them into months. I know it can be done, but I haven't been able to figure out how to do it.
View 9 RepliesI have created a pivot table and grouped the dates by month and year. Unfortunately I did not realise I left the auto box clicked which has inserted an end date. Is there any way I can change this easily so that I can insert more data.
View 3 Replies View RelatedI have a weekly spreadsheet that has downcodes for components and every week I create a pivot table to break the file of downtime by truck# and downcode. There are about 80codes and they are classed into10 classes but there are weeks when some of the codes in a class won't appear so the code shouldn't show on the report. What I do on a weekly basis is create groups for the codes captured by the pivot table......Example
Class Code
Engine 601
602
614
616
626
647
Frame 609
611
625
656
ETC
I have a pivot table below: Excel 2010. All of the information within it is text, not numbers, as illustrated below as well.
Below is what I have now....
Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green
Column C, Row 2 = Green
What I want it to look like is below, removing the second word "green" from column C.
Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green
Is there a way to do this and do it automatically? If I was doing this in a regular excel sheet, I would have a merged cell in column C, rows 1 & 2.
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I excel Pivot table which i am grouping by Months. I need to show year next to the Month in grouping. Is that possible?
View 1 Replies View RelatedI am having issues grouping dates by month in the attached example. The date field is formatted as text. When I attempt to group in the pivot table, I am not given the grouping dialog box. I can't figure out why.
View 8 Replies View RelatedI have a large spreadsheet which lists individual dates over two years and circulation figures next to each date. Sometimes there are two entries for a particular date e.g. 1/10/08 there were 150 readers of magazine A and 200 readers of magazine B. When displaying this on a graph it give a bar for each day, whereas I would like a bar for each week.
Is there a formula to convert the individual dates into weeks and then total the circulation figures for that week?
Column A = Dates
Column B = Circulation Figures
I am trying to group some dates in a pivot table.
Unfortunately I receive an error saying it can't group the selection.
I have done "text to columns" and made the cells - (date values) and I have had no luck.
As I am at work I can't use the HTML genie because it won't install but I have uploaded a dummy file to my dropbox which available from here [URL] .......
I pull this data from an Oracle POWER PIVOT and always have trouble grouping the dates when they come from Power Pivot. Currently working as analyst so figuring this out would make life so much easier as I could group by months etc.
One cool feature of 2010 is ability to (right click on dates) and Group them into Quarters.
How to change the Quarters to be in line with e.g. Financial Year, as opposed to fiscal years? i.e. have a start date of say June?
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies View RelatedThe attached file shows what I'm trying to do - I'd like to fill in the data in the top left corner table (blue-shaded) with the data combined from the two other tables. Think of the Greek letters as names that are grouped into the groups named {A,B,C,D,E} (the names have no significance, it's just an example).
I was able to do so using an array formula and an extra column (yellow shaded) as you can see in B2 - however, this solution doesn't suite my case because the actual tables will be coming from automated processes and cannot be easily manually altered.
I tried inserting the array formula into the sumproduct formula - see B3 - but for some reason this doesn't behave the same as when the array is outside the formula. I should also mention that I came across a user defined function solution - this too is not so suitable for me right now.
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.
Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.
Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.
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.
I have my date list in a2:a503 with weekly dates ie. 2/12/2009, 9/12/2009, 16/12/2009 etc etc,
what I would like to do is 1st group all these dates as dec 09 in b2:b503, 2nd point is I would like to have cell c2 to have lowest date (in my example 2/12/2009) and d2 to have the highest (which is 16/12/2009) of that month
ie start date 2/12/2009 end date 16/12/2009.
I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"
End Sub
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
I have a table with model numbers as column A, and Row 1 contains dates, each columns contains net sales, my question is there any simple way to group all dates into months and years, i have tried using a pivot and it doesnt work correctly Screenshot (1).png
View 2 Replies View RelatedI am trying to generate a formula that looks at dates in one column, finds all the same days, then looks at another column and adds the values for those dates.
I formatted it below for simplification but on the actual sheet the columns would be A, E, K respectively
DATE
DURATION
9/2/2011
1
9/2/2011
1.2
9/2/2011
3.2
[code]....
i have try to find the VBA source to group a range of value (not manual group it)
say example
1-10, 11-20, 21-30 and etc..
i have try to using macro to record my grouping step to a pivot table as below
Selection.Group Start:=0, End:=30, By:=10
but anyway, i think this is not a good idea.
any other suitable VBA code rather than the above code for the pivot table grouping?
I have a large spreadsheet, which has a pivot table, this is grouped by Date (Months & Years). However whenever I refresh the table the grouping is removed. I try to re group the date, but I get a message saying "Cannot Group that selection"
View 2 Replies View RelatedThe attached is the format of a pivot table i have made from a data. MY primary group is the REGION inside the region i want to group as per the LOCATION HEAD.
This report automatically sorts as per the location id.
How is it possible under region i want to group according to the location head and total. i DONT WANT TO CHANGE THE SEQUENCE OF THE COLUMS AND THE FORMATTING
Have a simple Pivot table that i have grouped in 14 day buckets.
Currently, the date range looks like this:
1/21/2013 - 2/3/2013
However, I want the range to show the 'last' date only.
Is there a simple way to do this? I just can not see it in the format cells menu...
Gary Pivot.jpg
I have a table with outstanding days for Funds owed by multiple accounts which dynamically changes everyday I am looking to group this table by >5days, 6 to 14 days , 16 to 40 days ,>40 to 90days and >90days consistently even though the table will change dynamically every day..
View 4 Replies View Relatedfrom the Forum on building a macro and was kindly told that a Pivot Table would do the job better, which it does. However, my table has a column of several dates, I was wondering the best way to group the dates into months rather than showing the full date, eg, September instead of 16/09/08 or August instead of 01/08/08.
View 4 Replies View RelatedI have the attached Pivot table. When i create this pivot table, it groups some of the "A# and Name"s.
e.g.
"A277003 / VICTORIA WILSON"
"A444302 / YASEMIN KELES"
Is there for the pivot table to not group by the A# & Name and to list the A# and names on each row...
Please see Output sheet to see exactly how I would like it?... But i would like this to be done in the pivot table so i can create a macro..... to do this..
I know how to get the pivot table in the my database to group patients by age. However it groups them like this:5-14
15-24
25-34
35-44
45-54
55-64
65-74
Medically it doesn't make sense to group pediatric patients with adolescents and adolescents with adults. What I would like to do is to get the pivot table to group patients the way I want them. Something like this:5-12
13-18
19-25
26-35
36-45
46-55
56-65
66-75
76-85
Is there a way to get a pivot table to do this? Or is there another solution?
I'm trying to create a pivot table to figure out categories of expenses over the last year. The problem is when i create this pivot table on the horizontal i see all the days of the year, as opposed to categorized by month. I've tried to 'group' the months together but not very successfully. HOW to do this grouping elegantly?
I've attached 2 screenshots.
Pic 1 - Original State of the table with all the days on the horizontal
Pic 2 - an attempt to group by month, gone wrong
I'm looking for a quick way of counting the proportion of records in certain number ranges e.g.
Number of records in range 1 to 50, 51-100, 101-500 etc.
i have a pivot table but I'm just not sure how to create these groupings. Is a pivot the right way to do it - or should I be doing something else with the source data.