VB Data Sorting Macro Unable To Sort Correctly
Jul 12, 2009
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
View 12 Replies
ADVERTISEMENT
Oct 23, 2007
I have a column with a value resulting from a fairly complex formula:
I will not include the formula, but basically what it does is a number of calculations including subtracting dates, that is
TODAY'S DATE - A GIVEN DATE (provided in a different cell)
That means that, either manually or automatically, those values are constantly recalculating (with every second that goes by, that value changes).
And the number of values/cells to update is not few: about 1100.
Here's the problem.
When I press SORT, Excel seems to be absolutely incapable of sorting that list correctly. The values are completely and randomly out of order.
I wonder if it could be due to the fact that given that those values would be constantly changing, there would be a conflict with the commands
sort
update
View 9 Replies
View Related
Oct 22, 2009
I need the macro I created to run (when assigned to a button) in specific sheets in the workbook, additionally if the workbook is saved under a different name by the user, the macro will continue to work. There are 10 sheets. I need it to run in sheets 6-10, when the button is clicked in sheet 5.
Then end goal in mind is to assign this macro to a button, so the user ,when ready ,can click the button to produce the results.
Background & Current Results :
My macro is simply an advance auto filter that copies the filtered data from sheet 3 to sheets 6-10 (non-unique values).
When applied per sheet , it works . When I modify the macro (I've tried several ways based on info published here) , the best result i can get is the data copies 6 times in the same page ( whatever active sheet is selected.). I have not assigned the modified macro to any buttons, why bother if I can't get it to run in the 1st place .
I suspect I need to redo the whole thing to have it simply copy the filtered data repeatedly to sheets 6-10 , but I am not that savvy to figure that out .
My Current Macro that Works Per Sheet
Sub tcktoriginal()
'
' tcktorginal Macro
' macro to run tickets
'
Range("F4").Select
Range("cpt.code.table.").AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:S3"), CopyToRange:=Range("A11:S11"), Unique:=False
Range("F5").Select
End Sub
Examples That I have Tried and Didn’t Work ........................
View 9 Replies
View Related
Nov 30, 2006
I am having trouble getting this list of data sorted the way that I would like.
Here is the data:
View 13 Replies
View Related
Oct 4, 2011
i have encountered a problem which happens when you write data that contain : in an excel sheet (i use excel 2010)
for instance if i enter to one of the cell 45:58 excel sees it as 01/01/1900 21:48:00 when i try to get the information by using a function i will get the wrong data for example typing in the different cell LEFT(Cell,5) will result 1.908
(i receiving the data from an outside source in this way and i need to make analysis)
i have noticed that the first 2 digits (21 in the example) are related to the number i have choosen in a 24 hour cycle for instance
24:58 will result 01/01/1900 00:58:00
26:58 will result 01/01/1900 02:58:00
48:58 will result 02/01/1900 00:58:00
View 7 Replies
View Related
Jul 31, 2013
I have three columns expressing death dates (date, month, year). The months and dates are finally sorting correctly within the same year, but I have it set to sort by year first.
The column starts off fine with 1860, goes up to 2013, then starts back at 1884 and goes back up to 2009. I can't find any difference between the two groups: there are some of the same years represented, there are blank month and date cells in both. I have a worksheet with birth dates that sorted fine; the only difference I can find is that birth dates only go up to 1997, but the column is formatted as General, so the turn of the century shouldn't be a problem.
I've tried highlighting the whole thing before sorting as well as selecting one cell as usual. I've also tried formatting the column as Text. I can't think of anything else.
View 14 Replies
View Related
Apr 11, 2007
I'm using the excel built in function to sort columns with my macro. However since my columns have 64,000 entries it takes a while for the computer to sort it.
View 14 Replies
View Related
Oct 29, 2013
Using Excel 2007, I have a very simple spreadsheet made up of only two worksheets that I am using to track the songs played by a band during a current tour.
One worksheet is called "Summary" and the other is called "Setlists". In the Setlists worksheet I list every show played (10 shows so far), and every song played from each show. In the Summary worksheet I have very song listed that has been played during the tour in one column (A), then the number of times that song has been played in another column (B). Column B, the number of times each song has been played, is populated by a COUNTIF function that looks at the Setlists worksheet and counts each instance of each song.
What I want to do, very simply, is to sort on column B in order to display the list of songs from the most played to least played. That is where I run into a problem. When I sort my list of numbers, I end up with a seemingly random list that certainly isn't from most to least and I can't figure out why.
Is it possible that the COUNTIF function which populates that column of numbers is somehow throwing off the sort?
View 6 Replies
View Related
May 12, 2006
I'm try to get the pos. placing in a auto sorted table to remain in place
i.e. 1st next to the top position. 2nd next tothe next position and so on down. but it keeps getting it wrong! I've tried amending the macro serval times but it makes things worse! Sorry can't attach the file, too big for this forum!!!!
View 2 Replies
View Related
Dec 30, 2013
I have a sheet protected and filters enabled. I use this code to enable sort and filters:
[Code] ........
Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab (you might need a password)."
View 5 Replies
View Related
Jan 30, 2014
I am trying to create a "Master Sheet" where I enter in the column data and after I have entered my data for each row, I can select the button which toggles the macro to run. I have it built to build new sheets as new clients are obtained. My problem is after I have a sheet that has client's data I cannot get new data to add itself below the data that is already there. I want each client's sheet to keep adding rows as more data comes in. My current macro is :
[Code] .....
Attached File : Data Entry Macro.xlsx
View 3 Replies
View Related
May 9, 2006
I am a mechanical engineer by trade and am working in Excel to manipulate data. I have written macros in the past, but have come to a stumbling block. For simplicity say I have a column of timing information such as:
Time Sequence
1
9
15
23
Then I have another column of sequential time:
Time actual
0
1
2
3
4
5
etc.
I want to be able to take the first two values of the first column, search the second solumn for any values between those numbers, then create a new spreadsheet and copy those rows to a new spreadsheet, then loop back to search for the next two sets of numbers. So I want to search my actual time for values between 1 and 9 and put them all in a new sheet, then search for values between 9 and 15 and put those in another new sheet, etc.
View 9 Replies
View Related
May 18, 2009
I have data being fed from an application where it's difficult to sort the rows into ascending order. So is there a way that VBA can sort them and produce a value according to the order?
For example, Column A contains 5 rows.
Row 1's value is 4
Row 2's value is 1
Row 3's value is 2
Row 4's value is 3
Row 5's value is 5
If I were to sort these into ascending order, I'd get 1, 2, 3, 4, 5 in Column A. But I can't sort the rows so I need code to tell me which is the lowest number (1), which is the second lowest number (2), etc.
The purpose of the code will be to put values in Column B according to what's found in Column A. For the the lowest number, 1, the value in the cell next to it in Column B would be "a". For number 2, it would be "b", etc.
View 9 Replies
View Related
Apr 15, 2014
I have two columns, left is called OD right is called ID. In each OD there are multiple ID's but this is shown for example as:
OD-----------ID
254000----0
254000----127000
285750----0
285750----127000
285750----158750
304800----0
304800----127000
Therefore, showing duplicates for the OD. The required format is:
254000---0---127000
285750---0---127000---158750
304800---0---127000
This shows each OD in the first column (no duplicates) and the corresponding ID's in the columns on the right. The example is done with small amount of numbers however, the data I am working with consists of thousands of rows therefore is not practical to do by hand. Please see attached spread sheet for example template with both unsorted and sorted data shown.
I attempted to do this by using the following code:
[Code] .....
Attached File : testing.xlsx
View 3 Replies
View Related
Jul 28, 2009
here i have results from a race. the racers have numbers in the format of a20,c51 etc
then the name of the competitor and car make model.
then some times that they run. in the format of 11.80 secs the following times have either thousands or hundredthousths of a second which i only want to comma seperate the 2 decimals before and after so a 12.3456 will be ,12.34,ignoring the 56 then the next result and so on.
so i want to comma seperate the a20,andrewspeck,vauxhalltigra,11.27,13.36,12.87,12.91 etc etc
now the name and vehicle will cause huge sorting head ache so leave those together i can seperate them manually.
.
A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128
2A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.80120
A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112
A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119
1A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.18111
A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113
1A8Gareth BirchVauxhall Nova12.1412.1411612.769912.18119
A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114
1A7Andrew SpeckVauxhall Tigra12.2612.26131
A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652
1A14Westan FrickerFiat Coupe Turbo12.7812.78110
A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109
View 9 Replies
View Related
Jun 28, 2013
I want to sort columns A,C:P and use column C as the column that I sort and the other rows will move with column C. I want column B to stay and not move with the sort. How can I do this.
View 2 Replies
View Related
Aug 29, 2006
Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.
I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was ....
View 9 Replies
View Related
Jun 1, 2007
In the attached workbook, sorting the dates in column M results in absolutely nothing happening. The dates are formatted as dates (dd/mm/yyyy). The dates in column M are arrived at by adding a number of days (formatted as Number) to another date, the value of which was determined by an array formula. When I retype the actual date into another column and sort that, I get it sorted. Why does the other sort not work? BTW - I actually need to sort column M with column N.
View 2 Replies
View Related
Nov 21, 2008
In column A I have alpha-numeric data similar to:
2
A1
3
A20
A186
B4
B13
1
C5
C3
And I need to sort them so that they are like:
1
2
3
A1
A20
A186
B4
B13
C3
C5
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
View 6 Replies
View Related
Nov 21, 2008
In column A I have alpha-numeric data similar to:
2
A1
3
A20
A186
B4
B13
1
C5
C3
And I need to sort them so that they are like:
1
2
3
A1
A20
A186
B4
B13
C3
C5
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
View 5 Replies
View Related
Dec 2, 2009
I have this module that is supposed to look in column A and if it sees duplicates it looks in column B (with the date) and erases the entire row where the date is older in column B. I am attaching a sample to better illustrate it.
The highlighted in yellow rows are were I have duplicates and I want the row where the date is in red border to remain.
The module I have so far erases the newer records instead of the older.
This is the file:
Sample sorting.xls
Here is the
View 3 Replies
View Related
Sep 11, 2006
I have a column containing 365 entries, one for each day of the year. This I intend to send out to Army bands, to have them fill in their forecast of engagements. I do not want them to be able to amend the original diary, but to scroll to the bottom of the list, add a new entry with the applicable date and then be able to 'sort' back to chronological order. This would then allow a separate entry for every engagement to allow me to interrogate the spreadsheet. As I have protected the sheet, the 'sort' facility does not function even though when protecting I ticked the box to allow users to 'sort'.
View 2 Replies
View Related
Jan 30, 2008
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
Jul 15, 2006
i have tried 2 different ways of sorting my worksheets in alphabetical order. Each time i try these it does something to all the sheets where it's not letting my For statement to run correctly. It was running correctly before I added the Sort Sheets macro. My workbook has a bunch of sheets for every agent and one summary sheet. The for statement looks in A1 for the condition. the condition is in every sheet except for the summary sheet.
Here is my For statement. Its was looking in A1 for the criteria before and working fine but for some reason it now is finding the criteria in the "summary" sheet when there criteria is not there?!?!
Sub run_it()
If Range("A8") <> "" Then
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
End If
Dim month As Integer
Dim month1 As String
month = InputBox("Enter the MONTH (Number) you are reporting")
If month = 1 Then
month1 = "January"
ElseIf month = 2 Then
month1 = "February"
ElseIf month = 3 Then .............
View 4 Replies
View Related
Nov 25, 2013
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.
View 4 Replies
View Related
Jun 27, 2009
I need to sort the following data by the CODE number shown while keeping the Description and Quantity attached to the various codes. (Excel is smart enough to first sort the codes numerically then alphabetically, which is what I need) The client info (bottom) also needs to be left alone. Done correctly, after running the macro, the order would be 04cls, 04fba, 08jud, with the Desc. and Quantities moved with the codes. At the moment each line is stored in a single excel cell.
Description = Item1
Code = 08jud
Quantity = 1
Description = Item2
Code = 04fba
Quantity = 1
Description = Item3.........................
View 9 Replies
View Related
Jan 16, 2009
I'm attempting to create something that can be used on any workbook open to automatically open a file based on the data in cell.
1) User right clicks on a cell.
2) If the cell's value matches an expected format (##-A-####-## for example), a macro is available in the rightclick popup menu.
3) The user selects the macro in the popup menu if they want to run it.
4) The macro takes the cell value and converts it into a filename.
5) The specified file is opened in an external program.
View 4 Replies
View Related
Mar 31, 2008
I am looking for a macro to do the following:
Once data is copied to a tab labeled "data", it will sort on column H and delete all rows of data that do not have a AI or an RI code on it. Is this difficult to do?
View 9 Replies
View Related
Dec 22, 2006
how to sort a selected range.
The end of the range could be any cell. The top of the range is fixed in A1.
I've tried with this:
Sub Order()
Dim final As Variant
final = Selection.End(xlUp).Address
Range("A2").Select
Range("A1:" & final).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _
("G2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub
View 9 Replies
View Related
Jun 29, 2008
i want to create a command button that would sort my data in descending order.
View 7 Replies
View Related
