Macro Sort A-Z, Exclude Some Text So It's Not Sorted
Jan 12, 2010
I've setup a macro button to sort some stockpile items from A-Z so entering quantity on hand can be done more easily. Each cell is a drop-down selection so at various times, there can be different stockpile items in the list, thus why, I setup a macro to quickly sort the list.
However, I've got some text (Select Stockpile Item) that I wish not to be included in the A-Z sort as this might put items starting with T or later at the end of the sort.
Sub SortMySpecialStockpileA2Z()
If MsgBox("Are you sure?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
Range("K4:L110").Select
ActiveWorkbook.Worksheets("MyStockpile").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MyStockpile").Sort.SortFields.Add Key:=Range("K4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("MyStockpile").Sort
.SetRange Range("K4:L110")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
Is there a way to run a macro after a worksheet is sorted (after sort event)?
Scenario: I have a several hyperlinks that use defined names to jump to locations throughout a worksheet. When the sheet is sorted the defined names don't update to the new cell locations (Microsoft KB 58245). I've written a subroutine that fixes the defined names but I don't know how to have the fix run automatically.
I prefer not to use Worksheet_Change without other conditions to prevent a lot of unnecessary calls to repair subroutine.
I'm trying to set up a dashboard with a table and some formulas so that I can just copy a data export into one tab and have the table perform various Vlookups, charts, etc. The trouble I'm having is that some of my cells contain a mix of text, and I'm looking for specific text only. Example:
My data source has thousands of rows, and I'm looking at the Tags column. I want to use a Vlookup that gives me the email when [Tags] contains "t2", but my search is getting confused with "impact2". Is there a formula to search for "t2", but not get confused with "impact2"? I considered using =NOT, but I don't want to exclude cells just because they contain "impact2". If there was a formula for =IGNORE, that would be marvelous. My formulas are getting longer & longer, but they always seem to break in the end.
Is there a way to do a Formula (SUM) but exclude cells with invalid values? I would like to add up all values in attached spreadsheet for cells A14 to A28 and again for A49 to A63, as well as get an average C-14 to 28 and again for C49-63.
As some of the cells contain text rather than numbers, the formula doesn't work. how to exclude these cells?
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
I have a speadsheet where I have a cell that reads a named range via OFFSET. The range consists mainly of numbers but a few cells contain the text UL (Unlimited)
The cell that "reads" the OFFSET is then used in another cell where it is multiplied by either 2 or 3 depending on user input.
The problem occurs where the cell attempts to multiply the text "UL" which results in "#VALUE!" How can I have this cell with the multiplication bypass the multiplication and simply return the text "UL" in the case of reading text from the named range.
I have a macro code (attached below) which i am using to do some conditional formatting for all my worksheets in a workbook. I would like this macro to skip 1st 2 sheets in this workbook.
Can some1 kindly edit my following code so that macro does not runs on 1st 2 sheets namely "Sheet1" & "Sheet2" and continue running for all remaining.
I have a workbook event where the macro inserts the username and date into the comments of the cell that has been edited.. However I would like it to exclude the 'Index' worksheet that has its own macro for indexing all the sheets in the workbook. As every time the sheet is clicked and the macro updates the sheet names all the cells comment.
I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.
I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
I have a column of data that contains alphanumeric ID codes, some of which contain no alpha characters (i.e., Excel treats them as numbers). When I do a data sort, Excel puts all the purely numeric codes first, then the alphanumeric codes. I want to sort the numbers among the alpha codes.
I know there's a way to do this, but I've spent half an hour looking and can't find the answer. And no, manually editing each numeric entry with an apostrophe is not an option.
I'm sure this has been asked many times before, but I can't seem to find an answer that works for my data. I have some data that is sorted by an ordered list (like a table of contents from an overly detailed book):
1.0 1.1 1.2 1.2.1 1.3 ... 1.11 ...
This list can be arbitrarily deep (although probably not more than ##.##.##.##), and because I'm using periods to separate the nested levels (can be changed), I've formatted it as text. This means when I sort the data using this column I get 1.11 before 1.2 (text sort order).
After lots of failed formula attempts I've resorted to using an additional column with hand-entered values to sort by so it will sort correctly:
1.00.00 1.01.00 1.02.00 1.02.01 etc
but I'm hoping someone knows a more elegant way to do this, or at least to generate this column without typing so many zeroes!
please see the spreadsheet, data are downloaded in bloomberg (I have broken the links already). I have some macro written to perform sort on every column by clicking heading in row 1,
I know excel usually stores the date in a number format but I can't change the date format here since data is downloaded in Bloomberg (I have also asked Bloomberg, format is fixed). But if I select the whole sheet and go to data -> sort, Redemption date as ascending then select "sort anything looks like number as a number", it works. I don't wanna do in this way, I would like the clicking label to sort, so in macro, I think I need to firstly convert the date into an integer then perform the sort, how can I amend the coding to accomodate this?
I have a data set of about 600 records. I want to be able to extract the Top 10% of the records and get rid of the other 90% - the values can change over time, so I always want to start with the full 600 records.
I am aware of the conditional formatting function that will identify the Top 10%, but I was looking for VBA language (does not have to be VBA - just automated), that calculated the 10% subset and either moved it to a different sheet, OR deleted the bottom 90% - in either instance the result being just the list of the top 10% of the data set.
I have a list of entries on one sheet. This sheet contains part numbers in random order. I would like to draw out the part numbers onto a new sheet, with no repeats and in numerical order. I want this to be done automatically without using a sort command. Also, I would like to avoid VBA if possible.
I have found two macros to do the job that I want. I would like them put into one macros What I want is to text to column and sort my data. Below are the two macros
I have a list of names in 1 column and I would like to be able to find out what name is most frequent and I'd also like to know how many times that name appears in that column.