Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by character
Hi to everyone in the newsgroup,
Does anyone to known how to sort a list by starting from any alphabetic letters? For example, if I chose "M", I'd like to have the entire list starting with all words that have as first letter "M" and after the other ones with "N" etc... Thanks a lot, Stefano. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by character
With your data in columns A:E starting in row 1, something like this should
do what you want: Sub ReSort() Dim eNdRo As Long Dim cUtRo As Long Dim x As Long eNdRo = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(1, 1), Cells(eNdRo, 5)) .Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With For x = 1 To eNdRo If Left(Cells(x, 1).Value, 1) = Cells(1, 6).Value Then cUtRo = x Exit For End If Next x Range(Cells(1, 1), Cells(cUtRo - 1, 5)).Cut Destination:=Cells(eNdRo + 1, 1) eNdRo = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(cUtRo, 1), Cells(eNdRo, 5)).Cut Destination:=Cells(1, 1) End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message oups.com... Hi to everyone in the newsgroup, Does anyone to known how to sort a list by starting from any alphabetic letters? For example, if I chose "M", I'd like to have the entire list starting with all words that have as first letter "M" and after the other ones with "N" etc... Thanks a lot, Stefano. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by character
On 17 Set, 11:43, "Sandy Mann" wrote:
With your data in columns A:E starting in row 1, something like this should do what you want: Sub ReSort() Dim eNdRo As Long Dim cUtRo As Long Dim x As Long eNdRo = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(1, 1), Cells(eNdRo, 5)) .Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With For x = 1 To eNdRo If Left(Cells(x, 1).Value, 1) = Cells(1, 6).Value Then cUtRo = x Exit For End If Next x Range(Cells(1, 1), Cells(cUtRo - 1, 5)).Cut Destination:=Cells(eNdRo + 1, 1) eNdRo = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(cUtRo, 1), Cells(eNdRo, 5)).Cut Destination:=Cells(1, 1) End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message oups.com... Hi to everyone in the newsgroup, Does anyone to known how to sort a list by starting from any alphabetic letters? For example, if I chose "M", I'd like to have the entire list starting with all words that have as first letter "M" and after the other ones with "N" etc... Thanks a lot, Stefano. Sorry mate, but I'm not very good in how to manage a code in excel, so could you tell me better how to run it? Thanks a lot, Stefano. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by character
Thanks for your instructions, but the code does not to work, it just
make an alphabetic order of column A. What I am doing wrong? Stefano Sandy Mann ha scritto: Press the function key F11 with the Alt key held pressed. This will take you into VBA. Now select Insert Module, this will insert a new white module. Copy the code I posted and paste it all into the white General module window. Enter data in Columns A:E with the names that you want to sort by in Column A. To run the code from a shortcut key select: Tools Macro Macros The macro ReSort will be highlighted, select Options and enter a key in the Shortcut key box and click OK then Cancel in the Macro dialog box. Now the Macro will run by pressing and holding the Ctrl key while you press your selected Shortcut key. If the macro does what you want then post back with details of where you real data is, whether it has a header row and what other data you have on the sheet - especially where we can *park* the sorted data when we are cuting it. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message ps.com... On 17 Set, 11:43, "Sandy Mann" wrote: With your data in columns A:E starting in row 1, something like this should do what you want: Sub ReSort() Dim eNdRo As Long Dim cUtRo As Long Dim x As Long eNdRo = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(1, 1), Cells(eNdRo, 5)) .Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With For x = 1 To eNdRo If Left(Cells(x, 1).Value, 1) = Cells(1, 6).Value Then cUtRo = x Exit For End If Next x Range(Cells(1, 1), Cells(cUtRo - 1, 5)).Cut Destination:=Cells(eNdRo + 1, 1) eNdRo = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(cUtRo, 1), Cells(eNdRo, 5)).Cut Destination:=Cells(1, 1) End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message oups.com... Hi to everyone in the newsgroup, Does anyone to known how to sort a list by starting from any alphabetic letters? For example, if I chose "M", I'd like to have the entire list starting with all words that have as first letter "M" and after the other ones with "N" etc... Thanks a lot, Stefano. Sorry mate, but I'm not very good in how to manage a code in excel, so could you tell me better how to run it? Thanks a lot, Stefano. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by character
Sorry I forgot to say, (can't you read minds? <g ) put the letter, in the
same that you want to start the sort on in cell F1. I see as well that there is an unfortunate line break in the Newsgroup posting and I found that if the case of the letter in F1 is different to the case of the initial letter of the list then it errored out. This new code should take care of both of these things. Delete the code and paste this one in, it will ensure that there are no breaks where there shouldn't be: Sub ReSort() Dim eNdRo As Long Dim cUtRo As Long Dim x As Long eNdRo = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(1, 1), Cells(eNdRo, 5)) .Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With For x = 1 To eNdRo If UCase(Left(Cells(x, 1).Value, 1)) = _ UCase(Cells(1, 6).Value) Then cUtRo = x Exit For End If Next x Range(Cells(1, 1), Cells(cUtRo - 1, 5)).Cut _ Destination:=Cells(eNdRo + 1, 1) eNdRo = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(cUtRo, 1), Cells(eNdRo, 5)).Cut _ Destination:=Cells(1, 1) End Sub Post back when you get it working. (or if you don't of course!) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message ups.com... Thanks for your instructions, but the code does not to work, it just make an alphabetic order of column A. What I am doing wrong? Stefano |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by character
On 17 Set, 13:23, "Sandy Mann" wrote:
Sorry I forgot to say, (can't you read minds? <g ) put the letter, in the same that you want to start the sort on in cell F1. I see as well that there is an unfortunate line break in the Newsgroup posting and I found that if the case of the letter in F1 is different to the case of the initial letter of the list then it errored out. This new code should take care of both of these things. Delete the code and paste this one in, it will ensure that there are no breaks where there shouldn't be: Sub ReSort() Dim eNdRo As Long Dim cUtRo As Long Dim x As Long eNdRo = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(1, 1), Cells(eNdRo, 5)) .Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With For x = 1 To eNdRo If UCase(Left(Cells(x, 1).Value, 1)) = _ UCase(Cells(1, 6).Value) Then cUtRo = x Exit For End If Next x Range(Cells(1, 1), Cells(cUtRo - 1, 5)).Cut _ Destination:=Cells(eNdRo + 1, 1) eNdRo = Cells(Rows.Count, 1).End(xlUp).Row Range(Cells(cUtRo, 1), Cells(eNdRo, 5)).Cut _ Destination:=Cells(1, 1) End Sub Post back when you get it working. (or if you don't of course!) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message ups.com... Thanks for your instructions, but the code does not to work, it just make an alphabetic order of column A. What I am doing wrong? Stefano Hi Sandy, your code is simply great!! It works very well! You saved me...! Many, many thanks again for your help, I really appreciate that. P.S. sorry for my late... but I have been very busy recently. Stefano. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by character
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
Can I create a special character for the Character Map? | Excel Discussion (Misc queries) | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) | |||
Excel and access should permit sorting by last character | Excel Worksheet Functions |