Sort A Table Of Data That Changes. Is Dynamic

Aug 28, 2006

am working on a spreadsheet that requires sorting a list of names and numbers. The numbers have a sum formula attached. I can set up the macro to sort the list fine by selecting the appropriate cells and creating the macro.

The code for this is below

Sub SurnameSort()
'
' SurnameSort Macro
' Sorts by mechanic surname
'

'
Range("A10:H13").Select
Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

However, I want the macro to still be able to sort the data when I add new rows to the list later on. In other words, the code above in bold somehow needs to be modified so that "H13" extends as far down as needed.

View 7 Replies


ADVERTISEMENT

Macro To Sort Dynamic List Into Table

Nov 16, 2008

I have a list of letting agents that I need to sort into a table to import into Access. The list is dynamic in that not all the fields are present for every record. I will have over 4000 records to sort out, so I would really like to automate this. If possible the macro should read rows from the data worksheet and write to columns and rows on the Table worksheet. I have attached a small example of the data and output required. To make it a little more challenging the column header name is part of the data and will need stripping out as well.

View 3 Replies View Related

Sort Data With Dynamic Ranges

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 in the table (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??

**Attached, please find the example spreadsheet, I have only written the code for the first table.

View 3 Replies View Related

Sort Dynamic Data Range

Apr 8, 2014

How might the code below be changed to handle any number of rows? Right now it's set (from a recorded macro) to go down through row 237, but I need it to sort anything from just a few to maybe a thousand.

Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C2:C237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("K2:K237"), _

[code]...

View 5 Replies View Related

Creating Dynamic List Of Data From Expanding Table Of Data?

Feb 13, 2013

I need to create a dynamic list from a table of data.

I have performance data for 110 different pumps. Data points are generated every hour, and the table is updated with new data periodically. I want to automate the population of a list of 6 different pumps, and specify the date range populated. To put it another way: I want to place data from Pumps 1 through 6 for all of November into Columns A through F. Then I want to clear that data, and show data for Pumps 105-110 for last week in those same Columns.

One of the main goals of doing this -- other than quickly narrowing a field of data -- is being able to quickly chart this data on a scatter plot with a custom format. The pumps are grouped together by region, and individual pumps have specific purposes. So I need to quickly generate graphs with a series' color scheme or formatting that is consistent and logical between different pumping regions.

I haven't been able to make pivot table work because of the graphing issue, and also because of the way it handles data points and presents data. I would like to make this work with excel functions and maybe some filtering, but I'm not opposed to figuring out a VB script if you think that's what I should use.

View 1 Replies View Related

Sort Table After Data Update..

Oct 13, 2009

I have a spreadsheet with 3 tables (all side-by-side). Table one has 8 rows of data; table two has 2 rows of data; table three has 3 rows of data. I am looking for assistance on how to sort each table in desceding order (by sales). Basically I want to do a sort everytime there is a change of values in any of the tables. I have attached a sample spreadsheet to clarify the table layout and the manner in which the values change within each table.

View 2 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

Column Chart Using Dynamic Data Table

Aug 4, 2014

I'm using a table with dynamic data to populate a column/line chart.

The data is based on monthly targets achieved and forecasted. With each month, the information will update. The graph needs to have only the current month and future months to be displayed and I don't want to include the previous months information in the chart. The information in the chart automatically updates and loses the previous month's data. Unfortunately, the graph plots the blank data and has a blank entry on the graph. This means that the information I need starts in the middle of the chart and has a line that shoots up from 0 to the current month's value.

Is there any way that I can omit the month altogether?

The data source I am using is below:

MonthTarget for month* (examples)

October 100,123,669
November 125,154,586
December 150,185,503
January 175,216,420
February 200,247,337
March 225,278,254
April 250,309,171
May 275,340,089
June 300,371,006

As you can see, July August and September are not included. I want to be able to start the graph from October in the above example. In the following month, I want to use the same data but October Figures will not be required. How can I achieve this?

View 9 Replies View Related

Dynamic Row & Column Highlights In A Data Table

Jan 15, 2014

I have a two way data table for which the two variables are changed frequently. In the data table, for ease of reference, i usually highlight the corresponding row and column of the two most current variables the intersection of which gives you the current output. But these highlights need to eb changed each time the variables are changed 9the table is copied onto another document for presentation hence the need to keep changing the highlights).

I was thinking of configuring the highlighting process to happen dynamically, i.e. the row and column highlights change dynamically when the variables are changed.

View 7 Replies View Related

Pivot Table - Dynamic Source Data

Sep 26, 2011

I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.

These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?

View 5 Replies View Related

Gathering / Referencing Data (dynamic Table)

Jan 3, 2014

I'm attempting to list my data... I have a dynamic table that has the following columns:

number of countries
country 1
country 2
country 3

1
Egypt

3
USA
Egypt
Scotland

2
Scotland
USA

As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.

I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.

The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"

I found the following formula (in E2):

Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)

Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.

View 1 Replies View Related

Summary Report Of Dynamic Table Data

Nov 10, 2008

I am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive.

An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix.

The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time.

The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.

View 9 Replies View Related

Move Automatically Data From Dynamic Table To Static?

Feb 13, 2014

I need to transfer data from a table dynamically updated every 10 minutes to a table that never changes and maintains data, how can this be done using a macro?

View 1 Replies View Related

Auto Sort A Table As Data Is Changed In Another Worksheet

Jan 20, 2010

My problem this time is in regards to auto sorting a table as data is changed in another worksheet and is auto copied within the said table.

I.E. I need the Fax page (second last), the table has to auto sort based on column " C ". This is easily done using custom sort but can it be done automatically ? I want to lock the page so nobody modifies it.

I attached a copy of my work so far, using Excel 2007.

All the sheets are locked except " Player DATA " and " Fax ".

The password is " Moose ".

View 7 Replies View Related

Dynamic Formula To Keep Column Of Data Aligned With Pivot Table?

Feb 23, 2014

In this file Vehicle Fuel Tracking.xlsm I have a pivot table set up to filter my data. Next to the pivot table I have a column that Totals the Mileage based on the MAX and MIN of each group. I am looking for a dynamic formula to keep the totals alligned if data is added or deleted from the pivot table.

I would also be open to changing the data table to accomodate this request if needed.

View 12 Replies View Related

Dynamic Comparative Matrix Table With Data Validation Conditions?

Jul 8, 2013

I have attached a sample data sheet which i am working on . ITs a comparitive matrix trable with Input validation between 0&2 . I also want the table to be dynamic .If i want to increase or reduce no. of rows and clumns i should do it some how . More details are mentioned in the attached file .

View 1 Replies View Related

Sort Table Of Data By Status And Automatically Populate Another Sheet

May 2, 2012

I am trying to sort a table of data by "Status" and automatically populate another Sheet. In Sheet 1, I have data as follows:

Issue Status Issue - 1 Open Issue - 2 Closed Issue - 3 Closed Issue - 4 Open Issue - 5 Closed Issue - 6 Open

I want Sheet 2 to automatically sort this information by Status = "Open".

Issue Status Issue - 1 Open Issue - 4 Open Issue - 6 Open

How I can accomplish this by Excel functions (I do not want to use Pivot table)?

View 4 Replies View Related

VLookup Function Which Takes Data From A Table In Closed File - Dynamic Path

Feb 23, 2012

I have a vlookup function which takes the data from a table in a closed file. A folder where the file would be located will change based on the value in the cell reference.

I used below formula which works, but every time when i drag or try to edit and enter, the path of the file path brokes and i need to reselect the path where the file is located. Is there a way where i can stop this?

Code:

=VLOOKUP(A2,'C:&E1&[Myfile.xls]Sheet2'!$A:$B,2,0)

View 4 Replies View Related

Macro Code Create Pivot Table Based On Dynamic Source Data

Nov 28, 2006

The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....

View 9 Replies View Related

Expand Table To Include Missing Times From Data And Sort Based On Multiple Criteria

May 6, 2014

I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)

How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.

View 1 Replies View Related

How To Create Dynamic List Within Dynamic Table

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

Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751
2.52
3.753
418
417
416

View 5 Replies View Related

Automatic Dynamic Sort

Jan 11, 2008

I have a spreadsheet that ranks the 500 components of the S&p 500 on an ongoing intraday basis. I would like to have the sheet sorted automatically as it is updated. I have very limited VB skills. Also, I heard there is a feature on excel 2007 that performs this function.

View 7 Replies View Related

Macro To Sort Dynamic Range?

Jun 6, 2013

I have a Ranking tab that has the persons name, weeks, months that subtotal into 2 columns, MTD and YTD. I have set up with 2 Macros to sort based on two columns based on their selection of MTD or YTD.

What I did not take into consideration is if a person was added at the bottom of the list.

Is there a way to modify this to include rows that may be added?

VB:
[SIZE=4]CODE HERE[/SIZE]
VB:
[B]Macro1
[/B]Sub SortYTD()

[Code].....

View 8 Replies View Related

Dynamic Sort Across Multiple Sheets

Dec 19, 2008

I'm trying to write a code to sort a variable-sized data range on several sheets. A sql query populates several sheets with data in N rows, where N varies based on query parameters).

I get a run-time error on the .sort command (method range of object global failed). i think this has to do with sorting by the same column on multiple sheets and haven't been able to debug it myself.

Sub sort()
Dim lastrow As Integer
Dim rows As Integer
Dim sheetarray As Variant
Dim colarray As Variant
Dim i As Integer

View 7 Replies View Related

Merge & Sort Dynamic Lists

Aug 5, 2008

I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)

Detail:
I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.

The Chicago range is defined as:
=OFFSET(Sheet1!$A$2:$A$11,0,0, COUNTA(Sheet1!$A$2:$A$11),1)
The Seattle range is defined as:
=OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)

In the boxes on the right of the lists, I just have a Data Validation List formula as:
=Chicago and the other as =Seattle

View 4 Replies View Related

Look At Column In Table Remove Duplicates And Sort To Another Table

Mar 19, 2014

I have a list of parts in a table. I am manually copy/pasting this list to another sheet in my workbook and then using DATA→Remove Duplicates to get a shortened list. Is there a way to automate this process?

In the attached workbook I want the yellow cells to auto-fill for me.

I get a lot of lists in the format on the left hand side so I want excel to automatically format them for me so that I can use the output on the right.

List Sorting Help.xlsx‎

View 1 Replies View Related

Sort And Filter Dynamic Range On Different Sheet

Apr 4, 2014

We have 2 dynamic ranges (input, output) on different sheets (sample data below).

Sheet1> Input
Dynamic range 5 columns

Sheet 2>Output
Dynamic range 4 columns
- Include only rows IN=1
- Sorted by LEVEL (BIG to SMALL) and BUY (SMALL to BIG)
- Keep duplicates

What formulas should I place on Sheet2 avoiding Pivot tables or VBA?

Sheet1> Input (16 data rows)
A B C D E
Level Buy Sell Firm IN
16620 4.00 null F1 OUT
16610 5.10 0.80 F1 OUT
16600 11.40 6.60 F1 1
16590 24.50 18.60 F1 1
16580 44.90 37.10 F1 1
16570 66.90 59.60 F1 1
16560 84.40 78.70 F1 1
16550 95.00 90.30 F1 1
16540 99.80 95.60 F1 OUT
16530 100.00 98.00 F1 OUT
16611 6.66 0.497 F2 OUT
16600 9.09 1.96 F2 OUT
16589.1 20 12.5 F2 1
16578.2 41.66 33.33 F2 1
16567.3 73.33 65.21 F2 1
16556.4 90.9 84.61 F2 1
16545 98.5 91.66 F2 OUT

Sheet2> output (10 data rows)
A B C D
Level Buy Sell Firm
16600 11.4 6.6 F1
16590 24.5 18.6 F1
16589.1 20 12.5 F2
16580 44.9 37.1 F1
16578.2 41.66 33.33 F2
16570 66.9 59.6 F1
16567.3 73.33 65.21 F2
16560 84.4 78.7 F1
16556.4 90.9 84.61 F2
16550 95 90.3 F1

View 6 Replies View Related

Getting Sort Function To Work On Defined Dynamic Range

Dec 13, 2012

Essentially i get a Runtime 5 error on the bold bit of code....

I am trying to define a range .... by using thexlUp function find the last row ( which works ) i then pass this variable into the sort code and get the error its probably very simple to fix, all it does i sort columnA but finds the last cell .... instead of the whole column,,,,

Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+e
NumberOfRow = Sheets("Sheet1").Range("A3000").End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Sheets("Sheet1").Range("A2", Cells(NumberOfRow)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

[Code] .......

View 2 Replies View Related

Create Dynamic Table From Static Table?

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







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