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.

View 1 Replies


ADVERTISEMENT

Sort Ascending Order Not Working

Sep 25, 2012

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]"        

[Code] .....

View 3 Replies View Related

Pivot Table Not Refreshing Properly?

Sep 18, 2013

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?

View 1 Replies View Related

One Of The Values On Pivot Table Not Calculating Properly?

Feb 3, 2014

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.

View 4 Replies View Related

Months To Be Sorted In Ascending Order In Pivot Table, Want To Use Multiple Colors In Pivot Charts

Sep 18, 2008

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.

View 9 Replies View Related

Reading Pivot Field Sort Order?

Jan 16, 2012

Is there a way to read the sort order of pivot fields or is it write only?

I already know how to sort a field, I want to be able to read the ascending descending property of a field so I can reproduce it later.

Like

?Sheets(SH1).PivotTables(g).DataFields(h).SortOrder
or

?Sheets(SH1).PivotTables(g).DataFields(h).Autosort.Order

expecting a Ascending, Descending

0r 0, 1, etc

View 1 Replies View Related

Keeping Field Order The Same In Pivot Table?

Dec 24, 2013

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.

View 2 Replies View Related

Pivot Table Columns In Month Order

Mar 17, 2007

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.

View 4 Replies View Related

Months Displayed In Proper Order In A Pivot Table

Oct 1, 2008

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)

View 2 Replies View Related

Pivot Table And Date Not Working?

Jun 10, 2013

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?

View 1 Replies View Related

Group Numbers Into Workable Ranges In Order To Run A Pivot Table (days Taken To Update)

Apr 28, 2014

Cells in row C have numbers (number of days between date a and date b)

I want to group the days so I can run a usable pivot table as follows:

Up to 7 days between update date and today (ie 1 week) 8-14 days between update date and today (ie 2 weeks) 15-21 days (ie 3 weeks) 4 weeks +

My attempt is as follows but only give two results and not 4?

=IF(OR(C2<=7),"1-7days",IF(OR(C2>=8,C2<=14),"8-14days",IF(OR(C2<=21,C2>=15),"15-21days","Over4weeks")))

View 3 Replies View Related

Pivot Table - Add Calculated Field Not Working

Apr 26, 2012

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.

View 3 Replies View Related

Manual Sort In Pivot Table

Aug 16, 2009

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..

View 8 Replies View Related

Combine And Sort Pivot Table

Jun 4, 2013

I have a table that looks like this:

Code:
TechNum | FirstName | LastName | System | WorkDate | Values

I need the pivot table to look like this:

Code:
-->System
--> TechNum - FirstName LastName
--> WorkDate

I need the sorting to look like this:

Code:
-->System
--> TechNum
--> WorkDate

How do I show the first three fields in the same level of the pivot table and still maintain the sort cascade?

View 1 Replies View Related

Pivot Table To Sort Data?

Feb 19, 2009

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:

DEV#WIRE#SWITCH-1A123AMP-1A124AMP-1A125AMP-1A126SWITCH-2A127

I need to sort it into this format in another sheet or another area in the same sheet:

DEV#WIRE#1WIRE#2WIRE#3AMP-1A124A125A126SWITCH-1A123SWITCH-2A127

View 9 Replies View Related

Grouping Dates Not Working On Pivot Table - ERROR

May 28, 2014

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.

View 3 Replies View Related

Custom Sort Of Row Label In Pivot Table

May 3, 2014

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?

View 6 Replies View Related

Sort A Pivot Table Based On A Range

Nov 12, 2009

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.

Is this something that would be possible to do?

View 8 Replies View Related

Sort Multiple Columns - Use Pivot Table?

May 14, 2012

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.

sort>sort>sort>sort>VEHICLECOLOURSMAX SIZESPEEDA9brown50100PRONTObrown100110VELICITOgreen200120
SPECIALpurple50155ZEROpink2585GREENVgreen5155MIDEOblack2000135DELVOpurple300155ASPIROblack6155

View 2 Replies View Related

Sum With Offset Not Working Properly?

May 30, 2013

Total amount in Column B should same as shown in Column F

=IF(E3<>"",SUM(OFFSET($C3,ISTEXT(E3),,,1):C3),"")Above formula is calculating always two rows.

View 2 Replies View Related

Vlookup Not Working Properly

Apr 10, 2009

I have problem with vlookup. I am attaching my file.

View 3 Replies View Related

Sorting A-Z Not Working Properly

Sep 3, 2013

I am including an attached file for reference.

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.

View 1 Replies View Related

Chdir Not Working Properly

Feb 5, 2009

I have a really strange problem i have two different hard directories o: & u:

i have the following code linked to a command button

chdir "U: foldername foldername foldername"
saveactiveworkbook.saveas range("c9")

View 6 Replies View Related

Selection Not Working Properly

May 25, 2006

I try the following selection

Columns("B:AF").Select
Selection.ColumnWidth = 2.3

It is selecting columns a to af. Why would take column a if it is not part of select?

Sub Add_worksheet_month()
Dim Lst() As String
Dim I As Integer
Dim wname As Date
'Dim wmonth As Date

View 4 Replies View Related

Sort Pivot Table Based On Partial String...

Oct 6, 2008

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.

View 3 Replies View Related

Dynamically Sort Generic Pivot Table Field

Feb 15, 2008

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 isnt 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.

ActiveSheet.PivotTables("ptDWDM").PivotFields("Project Name").AutoSort _
xlAscending, "Project Name"

So in the above code I dont want the reference to Project Name to be hard coded.

View 2 Replies View Related

Calculating Formula Not Working Properly

Nov 15, 2008

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....

View 6 Replies View Related

Rank Formula Not Working Properly

Oct 7, 2009

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.

View 8 Replies View Related

Ranking Formula Not Working Properly

Oct 25, 2009

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??

View 4 Replies View Related

MSForms.CommandButton Not Working Properly

Jan 29, 2009

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.

View 3 Replies View Related







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