Excel 2007 :: Source Date Sorting Into Format Used For Pivot Table / Chart?

Sep 24, 2011

I am stuck trying to sort data that looks like the following, into a format that I can use as source data for pivot tables/charts.

Excel2007ABCDEFGHIJK2MalayMalayMalayMalayMalayMalay
ChineseChinese3MaleFemaleFemaleMaleFemaleFemaleMaleMale
436-4536-4536-4525-3525-3525-3536-4536-455
DateArticleContentLapseCurrentCurrentCurrentLapseLapseCurrentCurrent612-SepRelieving 40 years - Oct 15, 1972
Thick Haze continues to blanket MalaysiaHistory42712-SepRelieving 40 years - May 5, 1972
All Sharifah wants is a pair of legsHistory1222812-SepA man and his agonyHistory3223912-SepA lesson on Sept 16History22Sheet2

I will need to group Data multiple ways. However, I cant work out a good way to sort it. Can I have a suggestion on layout that I will be able to arrange data appropriately.

View 2 Replies


ADVERTISEMENT

Excel 2007 :: Sorting Pivot Table Columns By Column Field Label (date)

Jun 30, 2014

I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.

E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".

Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.

I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.

i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.

View 5 Replies View Related

Excel 2007 :: Crashes When Trying To Update Data Source In Pivot Table

Sep 17, 2010

Excel 2007 crashing when trying to up date a data source within a pivot table?

View 3 Replies View Related

Excel 2007 :: Find Source Data Range Of Pivot Table?

Feb 4, 2013

how to find the source data range of a pivot table (that already exists) in Excel 2007?

View 1 Replies View Related

Excel 2007 :: Sorting Names In Pivot Table

Jan 3, 2012

I have created a pivot table using a family name in row labels. The names appear in alphabetical order unless the person's name is the same as a month or day of the week. The result is that I have Mr Sun and Mr May at the top of my list, rather than listed alphabetically. Sorting the list does not solve the problem. It switches Mr Sun and Mr May, but does not included them in the main list. Is there a setting I can use that will stop Excel 2007 thinking that these words are something that they are not?

View 4 Replies View Related

Excel 2007 :: Date Format While Copying Data From Another Source?

Jul 14, 2013

I have some data coming in from another source system which has the date format as "DD-MM-YYYY" but while pasting it to Excel (2007) sheet some date fields has just DD-MM-YY format which disrupts my macro. I did changing the language settings to "English(UK)" ,which i wanted, and changed the same in Excel options as well. But unfortunately it doesn't work.

View 3 Replies View Related

Excel 2007 :: Freeze Pivot Table Format After Clicking Refresh

Feb 25, 2009

Is there a way to freeze your pivot table format after clicking for a ' Refresh'. I'm getting tired reformatting my column headers to wrap or in the middle etc. I'm working in Excel 2007.

View 6 Replies View Related

Copy Pivot Table Format And Create New Data Source?

Apr 26, 2012

I have created a pivot table in a workbook which relates to data from 2011 - 2012 and this works perfectly. What I now need to do is to copy the pivot table (without the data source) to a new workbook for data which will be collected from 2012 - 2013. Unfortunately when I copy it and try and find the new data source it does not update the filters according to the data in the new workbook.

View 5 Replies View Related

Pivot Table Not Sorting By Date

Dec 27, 2012

I have a birthday list with name, client, birthday. I want to create a pivot table that groups by month. Report layout is tabular. Then within that month, name, client & birthday (in date order). So my end result would look like;

January Jim Jones Company 1/4
Then rest of January names 1/15

Etc.

Right now, I can't get the birthday to sort by date within the month.

View 1 Replies View Related

Pivot Table And Date Sorting?

Jun 12, 2014

I am having the hardest time trying to get my pivot table to sort right. I downloaded data over a 7 year period and its sorted by months (January-2005 etc). The issue I am having is excel is putting it into this format

January-2005 January- 2006 ......... February - 2005 February-2005.

I need it to sort chronologically

jan 2005, Feb 2005, March 2005...... Jan 2006, Feb 2006 etc.

What do I have to do to get this fixed. I would rather do not have to do it manually each time I need this type of data.

View 3 Replies View Related

Excel 2010 :: Change Pivot Table Source With VBA?

Nov 25, 2013

I am using Excel 2010 and have a workbook with two sheets - "Risks & Issues" which contains the source data and "Risks - Summary" which contains a Pivot Table called "Dashboard"

I would like to create a command button called 'Refresh Data' which will be on the "Risks - Summary" sheet. When I add a new line to the "Risks & Issues" sheet, I would click the button in "Risks - Summary" and it will update the Pivot Table range.

So far I've tried using some examples found on this site, although with no experience in VBA macros, I'm not really sure what they do; all I know is that they cause an error.

Code:

Sub Refresh_Click()
Sheets("Risks - Summary").PivotTables("Dashboard").SourceData = Sheets("Risks & Issues").Range("A2").CurrentRegion.Address(True, True, xlR1C1, True)
End Sub

[Code].....

View 1 Replies View Related

Sorting By Company And Date In Pivot Table

Feb 1, 2008

I have attache a file to use as a reference, the real file is over 10 000 rows long.
What I need is to be able to quickly make a summary of each company within a certain period (let's say monthly) and be able to calculate the sub total of the GST column. Every report that's created than needs to be saved and put into a seperate file e.g 'TFQ Sep 07'. I understand I need to use a Pivot Table but can some one just give me quick example of how I would do this?

View 2 Replies View Related

Excel 2010 :: How To View Source Data Of Pivot Table

Jan 6, 2012

I'm used to work with the Excel 2003 edition, however I just changed the version to 2010. As result of that I do have the following question:

How can you view the source data of a pivot table in Excel 2010?

View 6 Replies View Related

Excel 2007 :: Sorting Only Part Of A Table

May 11, 2014

I'm selecting only some rows in a Table trying to sort only these rows (e.g. rows 11 thru 75 in a 200 row table), but when I hit Sort Excel always overrides my selection by selecting all the rows in the table instead. The same thing happens in VBA. I'm using Excel 2007.

View 12 Replies View Related

Excel 2010 :: Pivot Table Sorting With VBA?

Apr 10, 2013

Using Excel 2010

I have a workbook with multiple sheets.Sheet1 is named "UPC" and is a giant database for my workbook containing 80,000+ rows and 12 columns.

Sheet2 is named "Scan" and uses a bunch of index-match formulas. Basically, you scan a barcode and it auto-populates across the row all the information it pulls with that particular UPC from the "UPC" sheet. One of my columns, "QTY Scanned", in the "UPC" sheet has a countif formula to keep track of how many times that item was scanned on the "Scan" sheet.

Sheet3 is named "PSlip" and has a big button on it that you press and multiple macros go into overdrive. First macro takes all the rows from "UPC" with a quantity greater than 0 in the "QTY Scanned" column and copies it to the "PSlip" sheet.

Second macro takes that data and makes a pivot table.This is all clothing, pants, shirts, jackets, etc...When my pivot table is created, my Column Label is Size. When the pivot table is created, it automatically sorts it in ascending order, so it goes from numbers to letters.

This is great for pants because the size is in reference to a waist size; 24,25,26,27, etc...

HOWEVER, shirts, jackets, and other tops are not in a numeric size: they are in a Text format. XS, S, M, L, etc...

Sometimes we only scan pants for an order, sometimes only tops, sometimes both.

Is there a way for the pivot table to recognize when tops are in included and automatically sort the sizes?

I still want the numeric values at the beginning, but once the text starts it automatically sorts in this order:

XXS, XS, S, M, L, XL, XXL, MT, LT, XLT, XXLT, 1X, 2X, 3X, 4X, 5X

View 2 Replies View Related

Excel 2007 :: Viewing SQL Statements Without Being Connected External Date Source

Jun 3, 2012

I now have XL 2007 but no longer work at the same place so do not have access to the external data source. I would like to view the SQL statements that I wrote back then but keep getting error that I am not connected to external data source and so cannot see my sql statements. I have MS Query 2007.

View 1 Replies View Related

Dates Pivot Table US Format But Source Dates European

Apr 4, 2008

there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.

I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.

View 3 Replies View Related

Create Pivot Table: Cannot Open Pivot Table Source File

Jan 4, 2010

I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.

View 14 Replies View Related

Excel 2007 :: Date Filtering - Change Date Format Supplied By Date Picker

Apr 24, 2012

I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.

I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).

When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)

So my questions are:

1. Is this due to the formatting?

2. Is there a way to change the date format supplied by the date picker?

View 7 Replies View Related

Excel 2007 :: Find And Format Text Within Chart Textbox Object?

Nov 15, 2012

I have a text box within a chart tab which is populated with text values from a worksheet within the workbook. That part works fine however i want to be able to achieve the following example:

Current Text: Component Name - Notes

if there are notes against the component name i want the name to remain in black and the notes to change to blue

Aim: Component Name - Notes

If there arn't any notes then for the component name and the "-" to change to a light shade of grey

Aim: Component Name -

There are 26 components, each on their own lines in the textbox so im thinking either a loop where the cell reference is variable eg. ("CY" & i) or using the .find with a variable.

My code so far is:

Code:
Sub Chart_Notes_NTCA()
Dim i As Long, j As Long, Counted As Long, Total As Long
Dim Ans As String, Notes As String
Dim Cht As Chart

[Code].....

View 9 Replies View Related

Excel 2007 :: Pivot Table Range

Apr 19, 2013

I prepared a Pivot Table and then i add some data in it but now all data is not showed by Pivot Table.

I do all necessary things like refresh and all but unable to increase a range.

How to increase range for Pivot Table in 2007.

View 3 Replies View Related

Excel 2007 :: Value Filter In Pivot Table

Oct 9, 2008

I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value?

View 9 Replies View Related

Excel 2007 :: No (blanks) In Pivot Table?

Sep 26, 2011

I cannot remember how/if I can ALWAYS exclude blank results from my pivot table.

I'm hoping i don't need to use a macro, it should be in the settings for the pivot table, I hope...

I use Excel2007.

View 2 Replies View Related

Excel 2007 :: Pivot Table Layout

Apr 12, 2012

In my data, I have:

1. Age (up to 20, 21-30, 31-40)
2. Gender (male, female)
3. Location (London, Paris, Berlin)

I want to see Age and Gender as column labels and Location as row labels. However, I don't want Gender as a sub-set of Age - I want Age Labels followed immediately by Gender labels ie:

Upto20 / 21-30 / 31-40 / Male / Female

I don't want: 21-30 Male / 21-30 Female / 21-30 Total / etc etc..

Is there a way to specify this in a pivot table?

View 3 Replies View Related

Macro And Pivot Table Excel 2007

Dec 10, 2009

In Excel 2003 I created a macro that copied data from a table, pasted into a new sheet, created a pivot table, copied and pasted that data next to pivot table, sorted, copied and pasted into chart data. I've used this macro in Excel 2007 with no problems.

Now I'm trying to create a similar macro but it won't complete the pivot table. My Pivot Table Field List should have the Row Label and Values populated but when I run it all that is populated is the Values field.

Is there something in 2007 that will not allow me to create a macro using a Pivot Table or am I doing something wrong? The code is below if that helps ...

View 9 Replies View Related

Sorting Chart Legend To Match Source Data Order

Feb 23, 2012

The order of entries in my chart legend make no sense to me. Why is it not matching the order of the source data?

View 1 Replies View Related

Pivot Table - Format Date In A Cell

Jun 7, 2013

I'm trying to copy the value of one column to another, column A has date and time in this format: 6/1/2013 6:35:00 AM and I'm trying to get ONLY the dates to another column so that when I do my pivot, I can sort it out by date. I tried =A1 and I can format the column to how I want it but the value of the cell still remains in date&time. Is there any formula I can use? or should I change anything in my pivot table?

View 4 Replies View Related

Excel 2010 :: Add Another Data Into Pivot Table Chart?

Jul 30, 2012

Can Pivot Table chart add another data from another sheet? I attached the link for this file (Add data1), it is because the file consists of several sheets and I do not know how to show here.

I would like to add the data from the "Rate" sheet into the Pivot Table chart (Chart.PT). I made an example by using normal way (Chart.Case (9)), the column series in the chart is the one I added from the "Rate" sheet. I wonder if I could do the same in pivot table chart.

View 9 Replies View Related

Excel 2007 :: Concatenate Cells From Pivot Table

Feb 8, 2011

I have a pivot table that summarizes jobs that need to be completed on a specified date. I need to put that information on a calendar, but I would like to concatenate all information from that date so I can do a vlookup from the calendar. My other problem is that the number of cells change for each date, so I cannot specify exact cells, (some only have 1 entry, but others may have 4 or more). Can I specify a range of cells based on the result of the pivot table?

I have attached the pivot table, calendar and the data sheet with the vlookup info. Am I using the proper calendar or method to find this info?I have struggled with this for over a week now.

View 4 Replies View Related

Excel 2007 :: Pivot Table Calculated Field

Mar 19, 2009

I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.

I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).

1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance

2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance

This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?

View 9 Replies View Related







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