I have some data formatted as a table. There are some blank rows at the end that still contain formula.
Every time I sort the data these rows move to the beginning. I have tried only selecting the active rows of data, but the whole lot defaults in each time
How do I sort data with a selected range only (within a table)?
Don't really want a macro as this will need to be used by others
I am trying to create a macro where users can rearange data in a table. I used the macro recorder to sort the data in colmn C in descending order and this is the code that I got:
VB:
ActiveWorkbook.Worksheets("By Client").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("By Client").Sort.SortFields.Add Key:=Range( _
"C3:C176"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("By Client").Sort
[Code]...
This works for the table as it is but the range may change as more rows are added to the table. I want it to start at row three as the first two rows are headers but in future tables the bottom row may not be 176. I dont want to include all rows as row 177 contains totals that I dont want to be included in the sort. what I should put as the range so that this macro will work regardless of how many rows are in the table?
I would like to know If we can sort the pivot table data in a bunch of Groups. e.g 50 years based on decades. currently I am able to group only once in a given range. but I would like to define several more ranges e.g 1970-1990 and 1990-2010 and etc
View 2 Replies View RelatedI can't seem to be able to find out how to sort data within a pivot table.
I have about 1000 different names that have data values for two years. I have a calculated field to show the difference....
i have generated a olap cube and display it using pivot table. But i am unable to sort my data from acsending order. For example: I had right clicked on my pivot table > field settings > Advanced > Select "Ascending". The data (sum of quantity) remains unsort after i have checked ok.
View 2 Replies View RelatedA web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:
When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.
UPDATE: see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.
On worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1 - A100).
I have Cells B1 - J100 with 3 options in each cell; Blank, C and NYC.
I need a list on worksheet 2 for each individual person showing;
(1) total number of cells marked C
(2) total number of cells marked C & NYC combined.
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
I am trying to sort by date in a table (It is named Linked_file_table), C173:I188. I would like the table to be sorted by I173 AZ.
View 1 Replies View RelatedI have managed to produce a table which uses formulas to calculate the amount of 'points' someone/a team has accumulated over a period of time however it seems that for some reason or another I cannot sort the table into order of 'who has the most points'
PosPlayerPlayedWonDrawLostPoints
1stDean120939
2ndJaymo1239018
3rdMacca1229115
4thRoss1219212
above is the final standings but as you can see the 'sort' function isnt working, and if i do any other sort the name of the person and the points no longer correspond... i.e. it ends up putting the names in alphabetical order and leaving all other columns as they currently are, or it changes the points column, leaving the names in the same place...
I've just come across Excel doing seemingly doing something really rather daft - though it equally could be me. I've grouped dates in a pivot table to 7 days at a time to group weeks, but when I then sort by date order it sorts by all those ranges starting with a 1st of the month, followed by all the 2nd's, etc.
View 3 Replies View RelatedTrying to format a pre-formated table. Would like to have all records for each item on one line so to be able to create a regular table and then a pivot table from it.
Pre-formatted table is attached as Samplesort1.xls.
I do not have SQL.
Is it possible to do this without macros?
I've got this table that I want to sort. I've got it formatted as a table in Excel 07.
ABDescription
11Sunbake
24Make Sandcastle
35Apply Sunscreen
46Sip Coconut Mocktail
52Play with kids
66Seashells
72Starfish
83Swim
91Snorkel
2Surf
3Boogieboarding
4Eat Icecream
1Play beach volleyball
I'd like to sort the table where all the 1's are together and in line with column A ie......
Is it possible to have dynamic sorting in a table?
What I am after is a scoreboard type effect where as results are entered, the teams are sorted automatically, rather than entering all of the details and then doing a sort afterwards.
I have attached a table where I would like the sorting of columns B - E done in the following order (All decreasing order):
Comp Points first
then by Sets %
then by Points %
I am drawing this info from another table. Will this have an effect on if it can be sorted dynamically? Hoping that as i enter a result in the other table, it will bring across the current comp points, set % and points %, and then sort the table into the rankings all at once.
I have created a pivot table that contains the following data:
Country Name
Date as in 5/1/08, 6/1/08, etc
With counts in the cells
I can sort on Country name and get an alphabethical list by country name showing teh numbers for each date
However I want to sort by the largest to smallest number that shows in the date column 01/01/08
Is there a way to sort on that colum-while moveing all the numbers in the other date columns along with it?
I'm trying to sort a table as displayed in the image below. The table consisting of items in column A, here fruits, and a column E where totals over three month is displayed.
I have entered an array formula in column H where the total of each fruit is summed and then sorted in descending order. My problem is that whenever two fruits have equal value (here, apple and pear), Excel will not manage the display of both fruits, it will just display the first twice. The formula in column I is a simple SUMIFS.
It doesn't matter if Apple or Pear is displayed first, but I need both items to be displayed in column H.
excel1.png
So i have a column full of first and surnames (both names in one cell). Is there a fast way to arrange them in alphabetical order? (surnames)
View 2 Replies View RelatedI 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.
Created the following macro, but doesn't seem to work.
Sub SortTable()
With Range("TC_Table")
.Sort key1:=Range("TC_Table").Cells(1, 1), Order1:=xlAscending
.Sort key2:=Range("TC_Table").Cells(1, 2), Order2:=xlAscending
.Header = xlYes
[Code] .....
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.
So, I have a table of scores from players for a category I have. It looks like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------Michael-----------Germany-------------364
2nd--------------Tim---------------USA-----------------364
4th---------------John--------------Norway-------------355
if one of the guys, for example, John were to improve his score to a 369, I'd change the score in Excel, then use the sort feature to put him above Michael and Tim. Only problem is, it would say his place is still 4th place, and it doesn't change to 2nd place.
I want it so the "Place" column automatically changes to the correct rank based on the score the person has. So if I changed John's 355 score to 369 and hit sort by A-Z, Excel would automatically change the "Place" he's in appropriately so it would look like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------John--------------Norway--------------369
3rd---------------Michael-----------Germany-------------364
3rd--------------Tim----------------USA-----------------364
I have a pivot table and i am trying to display the rows in order of relevance. I have a bit of code that will sort the rows into the order I want them which I have included. My problem is that sometimes I will have rows that will not be there, If position 3 doesn't exist I would expect it to display 1,2,4,5 but instead I get an error message.
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Nomination") _
.Position = 1
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Full") _
.Position = 2
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Equal") _
.Position = 3
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Target") _
.Position = 4
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("None") _
.Position = 5
Hello, I have a question about sorting information using dates but using a range. I need this to sort when I enter a date range into the spreadsheet (the blue blocks on the attached spreadsheet). The factors in colum G need to be sorted by month and input into the yellow highlighted area in column B. There may be an easier way of pulling the data from the "Data" sheet but this was the best I could figure out. Overall, January (or any of the months)needs to have the January (or appropriate) factor that fits within the date range that was input but sorted properly based on the months listed in Column A.
View 6 Replies View RelatedI'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 RelatedOptionally, to customize the sort operation, on the Options tab, in the Sort group, click Sort. In the Sort <Field name> dialog box, select the type of sort that you want by doing one of the following:
To return items to their original order, click Data source order. This option is only available for OLAP source data. To drag and arrange items the way that you want, click Manual.To select a field to sort by in ascending sort order, select Ascending (A to Z) by, and then select the field from the drop-down list. To select a field to sort by in descending sort order, select Descending (A to Z) by, and then select the field from the drop-down list. Tip Read the Summary section at the bottom of the dialog box to verify your choices.
My problem is this: after following the above steps, then clicking Manual, then ok, I cannot drag and arrange the row labels in the PivotTable.
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
I am doing a macro that will access a specific sheet of my workbook, a specific table, and will sort the table according to the field "Nominal", from largest to smallest. I have tried with this but it doesn't work:
Sheets("F.C.SSA-View 2").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("F.C.SSA-View 2").ListObjects("F.C.SSA_View_2").Sort.
SortFields.Clear
ActiveWorkbook.Worksheets("F.C.SSA-View 2").ListObjects("F.C.SSA_View_2").Sort. _
[Code] ....
In my example file are 4 columns. I placed auto filter to columns B and C. If column B sorted to ascending then this changes formulas in column D. I attached workbook also to understand my problem. If you try to sort column B to ascending you will see the problem in column D
View 4 Replies View Related