First Key Sort Order Not Working Properly In Pivot Table
Jul 23, 2012
I have entered a custom sort order under 'first key sort order,' but the field is not sorting according to the list.
I'm sure I entered the list correctly, because I actually entered it a long time ago, and it was working for a while. However, today the underlying data was temporarily erased, and the pivot table, when refreshed, basically went blank. I restored the data and refreshed the table, but this field is back to sorting in the wrong order. Refreshing the pivot table and re-choosing the appropriate sort options do not work.
In the below code, when I want to sort on Column "A" it is not working. Anything stand out that would prevent my code from working?
Sub Delete_Duplicates()Dim LastRow As LongWith Sheets("SAP Raw Extract")    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row    'Create ID Column        .Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove        .Range("A1") = "ID"        .Range("A2").FormulaR1C1 = "=RC[5]&RC[6]&RC[32]"       Â
I have a pivot table that I refresh using .refresh in the VBA code.
My problem is that when I loop through all the PivotItems in a filter list in order to make all visible, there are PivotItem names that aren't in the drop down menu and so the code breaks.
Do I need to refresh the pivot table slightly differently than just using .refresh?
This pivot table is used to track referrals for an employment service, and needs to show monthly totals of referrals, enrolments, cancellations and placements.
I have the referral date as one of the row lables, but it seems that each of the others now only reflectes back to that date rather than the date entered in that value
EG:
5 clients referred in September, One was placed in October, 2 in December and 1 in Jan but the totals for placed are all showing in September rather than the month the clinet was placed.
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
I have a table with two columns: 'Serial' and 'Read Date'. The table represents all those serials that failed to read at a certain time and date. For example:
Serial Read Date 4028543 10am Dec 16 4028544 10am Dec 16 4028566 10am Dec 16
[Code].....
I know you can move them manually but I have 73 reads so far and that's a lot of manually moving columns about.
I'd like to get Excel to use the original order of the Read date entries.
I have a couple of Pivot Tables that I create each month summarising my Income and Expenditure. The only problem I have is making the columns appear in the correct order, i.e. where July is month1 through June(the following year) being month 12 to coincide with my financial year. Oddly enough last month (January) was fine, following December.
This month January and February appear in columns 1 & 2.
I need to build a macro that creates a pivot table that displays data for the last 13 months, by month (e.g. Sep 07 - Sep 08). My table has a column for months and another one for years. My question is: to run the macro succesfully, how should I build my table, or make the pivot table, in order to get the months displayed in proper order (Aug 07, Sep 07, Oct 07, Nov 07, Dec 07, Jan 08, Feb 08,.......Jul 08, Aug 08)
Inside my raw data, there is a field for dates with the format of "mm-dd-yyyy". When generating the pivot table, the date is simply "mmmm". Where did the rest of the date field go?
I have and excel sheet that pulls data from an external source (ServiceNow) via MS Query.
From the exported data, I have the following:
Date opened (MM/DD/YYYY HH:MM:SS AM/PM) Date closed (MM/DD/YYYY HH:MM:SS AM/PM) calendar_stc (this exports in seconds, such as 136) (this equals date closed minus date opened for each record) assignment group
From this export, I'm trying to make a pivot table to show the average calendar_stc per assignment group, per month, per year; but in hours, not seconds.
I thought this was an easy process, create pivot to group row data by years then by months. Then add in assignment group to to the Rows as well. From there, I added to the values section of the pivot the calendar_stc. The default, of course, is to sum each row. But I need the average so I switched it to average. Now it is showing the correct average calendar_stc in seconds per group. To make it hours, I figured I could add in a calculated field. To do that I added the formula of =calendar_stc/60/60 for the calculated field. When I hit okay, it's giving me the SUM in hours for each row. Modifying the field settings to average does not change the numbers. So I went back into the formula and modified =average(calendar_stc/60/60). And that is not working either.
Here is an example to show the math with my formula.
I have a pivot table and if i select my headers i have the availability to sort a-z and z-a plus manual ...manual says to drag the item where i would like it... I have tried the excel database..they reference manual sort but really give no explanation.
Scenario.. I want to sort a-z.. then manual sort a few items to the top of the list..
I need to format some information in an Excel file, before exporting it. All the pivot table information I have found online is primarily for account management, using counts and sums. The data is currently in this format:
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.
Everyday I've to make Pivot Table from Data given by Branch Manager. When I create Pivot, my Row Label comes in order like
ICV TRUCK LCV BUS LCV TRUCK MCV BUS MCV TRUCK PICKUP & SCV shown in "automaticPivot" sheet in attached file. Later I arrange it in order of MCV BUS LCV BUS MCV TRUCK ICV TRUCK LCV TRUCK PICKUP SCV by dragging manually everyday shown in "desiredRowLabelSort" sheet.
Is there any way that sort Row Label automatically instead of manually OR any other faster way than doing manually?
I wonder if someone can tell me if it is possible to sort rows in a pivot table based on a range in the same workbook.
I have the pivot table set up as I need it but the values in the row change each time the macro is run (it is run when new data is added)
As part of the macro a list of Grades is imported into the Workbook. These grades are always imported in the correct order. As such I would like the pivot table to follow the order of imported grade list. The grades in the list will always be the same as the list in the pivot table.
I need to put a worksheet together that has multiple columns that I can then sort in order (on any one columns that affects each row together) in "vehicle type".
I want to set it up so the full listing can be sorted top to bottom in colour or then click sort to sort in MAX SIZE and or again sort in SPEED. (So sort in Alpabetical order or by value).
I havent used pivot tables before and assume its the best way. I dont want to use the filter system to sort as its too clumsy for the end user.
The file is sorted A-Z, but if you scroll down towards the bottom, you will see that some values are out of order. Both instances of University of Central Florida and University of North Dakota are out of place.
University of Central Florida should be between University of California and University of Colorado.
University of North Dakota should be between University of Colorado and University of Southern Mississippi.
I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,
Standard Kitchen Standard Master Bath Standard Hall Bath Optional Kitchen Optional Basement Bath
First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.
Is there a way to generically sort all pivot rows of a pivot table alphabetically each time the table is refreshed? I have a pivot table that pulls its data from a dynamic named range on a separate worksheet. As users add data to the range and refresh the pivot table the newly added items appear at the bottom of the table rather than alphabetically. I know how to specifically call out a pivot row and have it sort alphabetically (see code below) but I still want to allow users the flexibility of changing the fields and order of the pivot rows so specifically calling out a pivot row by field name isn’t going to work. I need to somehow add code that says: For each pivot row (1 to n, where n = number of pivot rows) sort each in alphabetical order.
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size.
But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly - since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
I have a rank formula that is seemingly erroring out on certain cells - indiscriminately. I have tried and tried to figure this out for myself but this is beyond my knowledge.
Only on some cells is the rank not working and returning a "-" (the error result).
I want the rank to use absolute numbers, so that it will rank according to variance or percentage (regardless of +/-). I am also using another cell (R1) to tell the rank what column to use.
As you can see there are a few entries, both + & -, that are not being included in the rank - for reasons beyond me.
I have 6 categories that need to be ranked 1-6 in order of highest number of occurances. My ranking formula is showing 1-7, missing number 3. I have attached a sample worksheet further showing what I am trying to explain. I need cell A3 to show a ranking of "3". Currently it shows "4". What am I doing wrong??
I have a form with 2 buttons and a frame. Inside the frame I have another button. I get a Type Error Message when I run the code and I don't understand why.
