Dynamic Sorting In Table
Jul 10, 2009
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.
View 10 Replies
ADVERTISEMENT
Aug 11, 2009
i have a table with 4 columns: (customer name, revenue, expenses, profit)
in cell A1, i can select from either of the above headings and what i wanted to do is sort the mentioned table based on the heading that's selected in A1.
for example, if i select Customer name in A1, then the table in (B2:E12) is going to be sorted by customer names (alphabetical)....or if i select Revenue in A1, then the table will be sorted by revenue values...etc.
View 9 Replies
View Related
Feb 20, 2013
I am trying to doctor a recorded sort macro to adapt to a changing range.
With the code below I get:
RunTime error 1004
Application-DEfined or Object-Defined error
I have tried with no luck:
Key:=dat
.SetRange dat
Key:=Range("A1").Resize(rw, clm)
.SetRange Range("A1").Resize(rw, clm)
Key:=Range("dat")
.SetRange Range("dat")
Key:=Range(dat)
.SetRange Range(dat)
[code]....
View 1 Replies
View Related
Feb 27, 2014
Here is my code.
Currently I get a 1004 error. "Method of Range of Object _Global Failed" on "Range(Selection).Sort.SortFields.Clear"
Code:
Sub sortBacklog()
Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myWorkSheet As Worksheet
Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("C:Backlog.xls")
Set myWorkSheet = myWorkbook.Sheets(1)
[code]....
View 1 Replies
View Related
Sep 2, 2006
I am attempting with a simple spreadsheet he can use on the golf course on a Treo (so no macros I believe can be used). I am want him to be able put the order next to a list of people's names (the same 8 play) and then a dynamic sort will take place listing who goes first. Everything works great except it one or more people do not play then the sort lists them as "N/A". Instead of "N/A" I want it to show up as a blank.
View 2 Replies
View Related
Apr 15, 2014
have data in the attached file arranged as follows:
"Source Data" sheet Data in columns A to L will be updated daily by pasting from another workbook. On any given day the data in columns A to L may have more or fewer rows than the day before.
"Lookup Table" sheet The aim is to return the data from the "Source Data" sheet; the selection depends on the Data Validation choice in cell O1.
I would like to be able to sort this dynamic list of data - ideally by clicking a single button - in descending order of the values in column L.
View 6 Replies
View Related
Feb 6, 2014
In the attached workbook I have hard-pasted data in columns A:M that's designated as an Excel Table to make it dynamic.
In the adjacent columns O:AA I have formulae to extract data from A:M based on the "Frequency" stipulated in cell AD1,
I would like to be able easily to sort the data in the variable sized range in columns O to AA in descending order of the calculated values in column Z.
Best way to do this so that the formula don't get jumbled up or the blank cells in column Z end up at the top of the sorted data?
View 2 Replies
View Related
Jun 8, 2014
I currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data (I can't used the named ranges as it should exclude the first row which is the header), is there a way I can make it dynamic too??
** The Europe table is from A1:G5 where Row 1 is the Header and Row 5 is the Total
Code:
With ThisWorkbook.Worksheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add Key:= _
Range("G2:G4"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
.SortFields.Add Key:= _
Range("F2:F4"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
[Code] ........
View 3 Replies
View Related
May 11, 2007
I regularly get a sheet that contains about 20 columns. I need to delete about 14 of them, and re-order the columns to a fixed order.
The columns always have the same headers, but are ofter in different positions (so I cannot hardcode the column location).
I have tried using the following, but fail on trying to use the result of the find in the selection afterwards.
I also do not know how to loop this through a list of column names (as I said, the input always has the same column names, and I always have to delete the same columns, and sort the remaining).
Sub Sort_My_Columns()
Cells.Find(What:="name1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
myRow = ActiveCell.Row
MyCol = ActiveCell.Column
Columns("myRow:myRow").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
End Sub
View 9 Replies
View Related
Oct 31, 2013
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
View 1 Replies
View Related
Oct 19, 2011
I have a static table that contains a list of all posible components for a particular product that we sell. The sheet successfully calculates the qty. of each component required for a particular install.
What I would like to be able to do is to create a new list able from the static one that only shows the components that are needed i.e. those that don't have a zero qty count.
I have seen some examples that come close on YouTube but can't find anything that does exactly this.
View 6 Replies
View Related
Aug 14, 2012
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?
View 3 Replies
View Related
May 23, 2014
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 Related
Oct 15, 2008
I 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...
View 14 Replies
View Related
Jun 9, 2009
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 Related
Jul 30, 2013
Trying 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?
View 1 Replies
View Related
Nov 24, 2008
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......
View 2 Replies
View Related
Jan 11, 2008
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?
View 9 Replies
View Related
Mar 17, 2014
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
View 5 Replies
View Related
Apr 28, 2014
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 Related
Feb 21, 2014
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 Related
Jan 13, 2010
I 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....
View 8 Replies
View Related
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
Jan 5, 2013
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] .....
View 7 Replies
View Related
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
Jan 6, 2009
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
View 9 Replies
View Related
Jun 2, 2006
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
View 2 Replies
View Related
May 30, 2014
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
View 1 Replies
View Related
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
Sep 18, 2009
Optionally, 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.
View 2 Replies
View Related
