Using Two Custom Sort Lists In Macro.
Aug 4, 2009
This is somewhat related to a post I had submitted previously but a bit more complex http://www.excelforum.com/showthread.php?p=2139259. I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, D1, or E1.
If the user "double clicks" on cell D1, I want to sort by column D using following custom list: AA1, AA2, AA3, A1, A2, A3, ZZ1, ZZ2, ZZ3, Z1, Z2, Z3. If the user "double clicks" on cell E1, I want to sort by column E using following custom list: AAA+, AAA, AAA-, AA+, AA, AA-, A+, A, A-, ZZZ+, ZZZ, ZZ+, ZZ-, Z+, Z, Z-
The issue I seem to be encountering is that I can’t use more than one custom list (e.g., if I use the column D sort list for column D, column E is also using the same list). I can’t seem to apply individual lists to each column. Would I need to “hardcode” the lists in my macro?
View 5 Replies
ADVERTISEMENT
Aug 3, 2009
I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, or D1. If the user "double clicks" on cell D1, I want to sort by column D but I want to apply a custom list. The data is in the "Custom Lists" table but I can't figure out how to apply to my code.
The sequence/sort order of the list is as follows:
aaa+, aaa, aaa-, aa+, aa, aa-, a+, a, a-, bbb+, bbb, bbb-, bb+, bb, bb-, b+, b, b-, ccc+, ccc, ccc-, cc+, cc, cc-, c+, c, c-, ddd+, ddd, ddd-, dd+, dd, dd-, d+, d, d-
code is as follows:
View 3 Replies
View Related
Jul 10, 2012
I am trying to sort a bunch of data by a custom list from left to right and after running the macro successfully excel crashes upon saving. I have tried this on different computers, resaved, started from scratch and it always crashes so there must be something with the macro that is causing this.
here is my macro:
Edit: It seems that the last line of code is causing my problem. I have started from scratch and gone though all the code one at a time and saved after each run. everything is fine until the "Application.DeleteCustomList Application.CustomListCount" is run. after that excel crashes when saved.
VB:
Sub Macro3()
'
' Macro3 Macro
'
[Code]....
View 1 Replies
View Related
Jun 20, 2013
I already have a Macro button built that hides values in column A. The next step I am trying to perform is a custom sort on column B in this order "Backordered", "Sourced", Shipped", and "Received". Here is my code that I have so far but where to begin adding in code to make this button sort.
Sub Inbound()
ActiveSheet.Cells.EntireColumn.Hidden = False
ActiveSheet.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = False
[Code] ........
Excel 2007/Windows 7
View 1 Replies
View Related
Oct 8, 2006
Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B
View 4 Replies
View Related
May 30, 2012
As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.
is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.
View 1 Replies
View Related
Jan 20, 2009
I've got a list of IP address in a column, between some others.
I basically need to copy the cell in a new column if the value match a criteria, and in an other column if not. e.g.
columnX new1 new2
a a b
b a b
a a
a a
a
b
it could be easy with a simple if condition, but that would create some empty cells.
So is anyone has an idea on how I could do that ? (I guess I could do it with VBA but I would prefer to find an easier solution as I'm not really confident with my VBA skills)
View 10 Replies
View Related
Apr 30, 2013
1) Imagine i have a row 1 with names, column 1 with bank account numbers, and under each name corresponding to each bank account it says "yes" or "no" depending on whether or not they are authorized on the bank account. How do i make 2 drop down lists, 1 with bank account numbers that produces the names which are authorized on the account and visa versa (list with names showing bank a/c numbers). It needs to be able to be updated easily.
2) Suppose I have the table as described above and another excel file with 1 column as bank a/c's and another column is authorized bank users. In this column however each cell just has a bunch of names for example one cell may contain (james, john, jack, jennifer, bill). Is there anyway to check these names against the ones in the table from the other spread sheet?
View 14 Replies
View Related
May 20, 2014
I am trying to sort a sheet based on the values in a column.
S, SI, M, MI. and it need to be sorted only in this order.
View 2 Replies
View Related
Aug 18, 2006
I would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I don’t know why its not working:
Sub CustSort()
Dim MyCount As Integer
MyCount = Application.CustomListCount + 1
MyValue = InputBox("Enter ID")
Application.AddCustomList Array(MyValue)
ActiveSheet.UsedRange.Sort _
Key1:= Range("A1"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=MyCount, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.DeleteCustomList MyCount
End Sub
find attached an example, try entering 300000 in the inputbox the custom sort doesn’t work
View 6 Replies
View Related
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
Sep 28, 2006
I'm wanting to be able to sort a table of data using a custom list for both the first criteria and the second criteria. I've setup my custom list correctly and it works perfectly for the first criteria, however, with the second criteria Excel just adopts its default sort order.
Is it possible to get it to use the custom list on the second criteria as well? Is there a simple VBA solution to this?
View 4 Replies
View Related
Feb 13, 2007
I feel I've searched thoroughly and wasn't able to find the right answer. I've attached a daily report example that needs the "Product" field to be sorted in a particular order. The order should be JEX, Q3791J, YOO5, KLX9, GHT (all similar products need to be grouped together).
Since the report is run daily the number of rows changes daily: one day can have 50 entries and the next 10. Is there a way to dictate the sort order and insert a blank row between each product grouping?
Once the Products have been separated into their groups, how would I sort each grouping by the "Term" column?
View 9 Replies
View Related
Oct 7, 2008
I update a daily incident report that is sorted by priority of the request as well as the date/time. In the past, I've recorded and run macro's to add a number to a priority so that it can sort numerically. Lately I've been tasked with building similar functionality into more reports. I am wondering whether I can tell Excel what order to sort items into (ie "Sort by HIGH, then MEDIUM-HIGH, then MEDIUM, then LOW)?
With regards to auto-fill lists (for want of the proper name), I report on multiple IT systems that fall into six "system" categories. Is there a way I can set up the "System" column to automatically update the correct data once the IT service is entered, rather than manually look up systems category then click the relevant system in the drop down box?
View 4 Replies
View Related
Mar 5, 2007
I have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets.
On my 'final list' sheet I need the first 10 rows to be left out of the macro’s so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does.
I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.
View 4 Replies
View Related
Mar 12, 2009
I have a Macro that I have recorded that autosorts several columns for me. This works fine as I have 5 columns that need to be sorted in a particular way.
The problem is that I have a custom list which I stored using the: Tools>Options>Custom List tab. Now this works fine on my pc as I have the custom list stored on my PC.however the problem is when someone else uses it on their PC it may no longer work as they won't have my custom list stored on their PCs.
Is there a way to store the custom list in a Macro and then use that list to sort to the criteria needed. For example "One, Two, Three, Four" will not store in alphabetically, so hope would I be able to sort so that they would appear in a chronological manner? Below is the code for the Macro I recorded. As you can see one of the lines says "OrderCustom:=6"; this I am assuming is reading from the list I created.
View 2 Replies
View Related
Nov 23, 2011
I have found many macros for sorting multiple sheets by a single column in Excel, however, need macro for sorting multiple sheets by column "A", then column "B".
View 1 Replies
View Related
Aug 25, 2013
I am trying to create a custom sort list by going to preferences/custom lists and typing 37 items into a new list in Excel. It will not take all of the items after I type them in. Is there a limit as to how many items can be in the list. It seems to only take 18-20 items out of the 37 and erases the rest.
View 9 Replies
View Related
May 28, 2014
I am having trouble finding info on this. I am trying to create a custom list to sort data.
I only want to sort two of the letters in my first Level or Column. For instance I want to sort Column A Alphabeticaly as A, B, C, D, but once I get to E, have the rest of the list in a random order that is sorted by my next level. Is there a wildcard character that can be used to have my next sorting level take over once I get to E?
View 4 Replies
View Related
May 13, 2009
I am trying to create custom sort list. It works below when I define range as A1:A79.
Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long
Application. ScreenUpdating = False
With Worksheets("Sort Order").Range("A1:A79")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With
Application.ScreenUpdating = True
End Sub
I have created a dynamic range called sheetsorder. If I revise my code it does not work.
Sub SortWS2()................
View 6 Replies
View Related
May 21, 2008
can anyone modify below macro to sort this data:
before sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-6-5-99-2-1-99-4-3
3-99-7-8-99-5-6-99-8-9
4-99-8-7-99-6-5-99-9-8
etc
after sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-5-6-99-1-2-99-3-4
3-99-7-8-99-5-6-99-8-9
4-99-7-8-99-5-6-99-8-9
etc
Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I
Set rRange = Range("B1:C10,E1:F10,H1:I10")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
End With
Next lArea
End With
End Sub
View 9 Replies
View Related
May 23, 2008
I would like to sort my column A by "Urgent, High, Medium and Low". However, when I click on sort, it obviously sorts alphbetically. Need to code to sort all colums A:K.
View 5 Replies
View Related
May 3, 2014
Everyday I've to make Pivot Table from Data given by Branch Manager. When I create Pivot, my Row Label comes in order like
ICV TRUCK
LCV BUS
LCV TRUCK
MCV BUS
MCV TRUCK
PICKUP &
SCV shown in "automaticPivot" sheet in attached file. Later I arrange it in order of
MCV BUS
LCV BUS
MCV TRUCK
ICV TRUCK
LCV TRUCK
PICKUP
SCV by dragging manually everyday shown in "desiredRowLabelSort" sheet.
Is there any way that sort Row Label automatically instead of manually OR any other faster way than doing manually?
View 6 Replies
View Related
Jul 10, 2014
Is it possible to do a custom sort on a column so that I can change the order the way the months are sorted. The order I'm looking for is:
October
November
December
January
February
March
April
May
June
July
August
September
Also the format for the cells in that column is 01-Jan, I don't want to see the year because I enter and sort the date as if the year doesn't matter so it automatically registers as 2014, if that makes sense. Basically I want that order of the months regardless of the year.
View 5 Replies
View Related
Apr 30, 2009
I have a large list of parcels to be delivered which have names, the last 2 letters of the postcodes, addresses tel. no. etc. I have created a custom sort list of postcodes in the order I want to deliver them. I can only list a total of 85 postcodes since there are 2 characters in each postcode and a return at the end of each line, hence 85*3=255.
Is there any way I can make excel sort my list of parcels using a spreadsheet list of the 2 postcode letters of unlimited length? There is a theoretical maximum of 676 postcodes in my area (26*26) so I would need it to be at least this long. It would also be more convenient if it were an excel based list since the editing of such a list would be more convenient.
View 3 Replies
View Related
Jan 23, 2009
I have a fairly large watch collection as below which I wear on a daily rotating basis(!).
FORTIS
Seiko
OMEGA SPEEDMASTER
Seiko
SINN 144
Seiko
Accurist
Seiko
TEMPTION
Seiko
OMEGA TRIPLE DATE
Seiko
Junkers....................
View 9 Replies
View Related
Apr 21, 2014
How to sort a column of data based on a custom list with more than 255 characters.
I have created a named range with 40 entries and then added data validation in the cells of column D using the above named range. However, I want to be able to sort column D in the same order as the named range but the custom data sort lists are restricted to 255 characters.
View 1 Replies
View Related
Jun 6, 2014
Is there any way to filter/sort a workbook by a specific text. (EX. Unit 17) I have a spread sheet with 40,000 plus rows and in 1 column it has descriptions. I am needing the filter to filter out all occurrences of Unit 17 and Unit 16. They will not always say the something happened to them. EX Repair brakes on Unit 17 or maybe repair tires on Unit 17...
View 3 Replies
View Related
Feb 17, 2014
Sort Example.xlsx
I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.
A8 = Customer
B8 = Load Time
C8 = Delivery Time
Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"
I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).
The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.
The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM
Example is attached (desired sort is reflected).
View 14 Replies
View Related
May 22, 2012
Basically, I have a sheet that shows time in Days going downwards (monday tuesday etc.) and is broken out by week. In Column C, I have Project Names. Columns G-O have numbers.
What I need to do is run a custom sort multiple times down the page so that each week has been sorted by Project Name (column C). I was hoping to be to just make a quick macro shortcut to run my custom sort and each new range I selected but I don't know how to make the macro run on the actively selected cells and not hard-coded cells.
View 2 Replies
View Related
