Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
I want to create a new custom list in Tools/Options that will sort a list of
numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Hi Connie
I can't figure a way to do with Custom Lists, but you could use a helper column. In the helper column enter =IF(LEN(A1)=4,A1&"-1",A1) and copy down Mark your data including the helper column, and sort by the helper column. After sorting, delete the helper column. -- Regards Roger Govier "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
just enter those numbers with comma delimiters
ToolsOptionsCustom List -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Sorry, I don't understand. This was only a sample list. We could be talking
about 1000s of numbers. Connie "Bob Phillips" wrote: just enter those numbers with comma delimiters ToolsOptionsCustom List -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
My spreadsheet data starts at A2, so I changed all the three A1's in your
formula to A2 in the helper column, but what it does is simply add -1 to all my numbers. Also, some numbers have -2, -3 and so on. The way Excel sorts such a list by default is that 1382 would come before 1085-1. I don't want that. The -1, -2, etc., indicate a revision to the original document and indicate whether first, second, third revision, etc. Connie "Roger Govier" wrote: Hi Connie I can't figure a way to do with Custom Lists, but you could use a helper column. In the helper column enter =IF(LEN(A1)=4,A1&"-1",A1) and copy down Mark your data including the helper column, and sort by the helper column. After sorting, delete the helper column. -- Regards Roger Govier "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Hi Connie
You needed to change the formula to =IF(LEN(A2)=4,A2&"-1",A2) It will only add a "-1" to cells which are 4 characters in length. Are you saying that some cells have numbers like 12345? It worked fine for me and gave the correct sort order with your data sample. Perhaps a revision to =IF(ISNUMBER(FIND("-",A2)),A2,A2&"-0") This way it doesn't matter about the length of the numbers, if there is already a hyphen in the cell, it will repeat it as is, if not then add "-0" to the number, as there will not be any revisions of 0 in the list. Sort on this helper column, then delete the helper column, -- Regards Roger Govier "Connie Martin" wrote in message ... My spreadsheet data starts at A2, so I changed all the three A1's in your formula to A2 in the helper column, but what it does is simply add -1 to all my numbers. Also, some numbers have -2, -3 and so on. The way Excel sorts such a list by default is that 1382 would come before 1085-1. I don't want that. The -1, -2, etc., indicate a revision to the original document and indicate whether first, second, third revision, etc. Connie "Roger Govier" wrote: Hi Connie I can't figure a way to do with Custom Lists, but you could use a helper column. In the helper column enter =IF(LEN(A1)=4,A1&"-1",A1) and copy down Mark your data including the helper column, and sort by the helper column. After sorting, delete the helper column. -- Regards Roger Govier "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Select the list, goto ToolsOptionsCustom Lists and hit the Import button.
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... Sorry, I don't understand. This was only a sample list. We could be talking about 1000s of numbers. Connie "Bob Phillips" wrote: just enter those numbers with comma delimiters ToolsOptionsCustom List -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
No, I think you have misunderstood what I am asking, or perhaps I have not
made it clear enough. If you had this list:-- 1383 1913 1957 2051 2135 2139 1769-7 1794-8 1849-1 1962-1 1977-3 1996-1 2009-2 2020-1 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2161-2 2169-2 ........and you want it to sort like this:--- 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 .......how do you create a custom list to make it sort that way. You will notice that in the first list, Excel sorts by putting all the -1 and -2 at the bottom. I want it sorted as you see in the second list where the first 4 numbers take precedence and then what ever number follows the dash. "Roger Govier" wrote: Hi Connie You needed to change the formula to =IF(LEN(A2)=4,A2&"-1",A2) It will only add a "-1" to cells which are 4 characters in length. Are you saying that some cells have numbers like 12345? It worked fine for me and gave the correct sort order with your data sample. Perhaps a revision to =IF(ISNUMBER(FIND("-",A2)),A2,A2&"-0") This way it doesn't matter about the length of the numbers, if there is already a hyphen in the cell, it will repeat it as is, if not then add "-0" to the number, as there will not be any revisions of 0 in the list. Sort on this helper column, then delete the helper column, -- Regards Roger Govier "Connie Martin" wrote in message ... My spreadsheet data starts at A2, so I changed all the three A1's in your formula to A2 in the helper column, but what it does is simply add -1 to all my numbers. Also, some numbers have -2, -3 and so on. The way Excel sorts such a list by default is that 1382 would come before 1085-1. I don't want that. The -1, -2, etc., indicate a revision to the original document and indicate whether first, second, third revision, etc. Connie "Roger Govier" wrote: Hi Connie I can't figure a way to do with Custom Lists, but you could use a helper column. In the helper column enter =IF(LEN(A1)=4,A1&"-1",A1) and copy down Mark your data including the helper column, and sort by the helper column. After sorting, delete the helper column. -- Regards Roger Govier "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
So I have to manually sort the list first?? I'm always adding numbers to
this list. I don't think you have understood, or I have not explained well enough. Please see my response to Roger Govier. Thank you. Connie "Bob Phillips" wrote: Select the list, goto ToolsOptionsCustom Lists and hit the Import button. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... Sorry, I don't understand. This was only a sample list. We could be talking about 1000s of numbers. Connie "Bob Phillips" wrote: just enter those numbers with comma delimiters ToolsOptionsCustom List -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Also, for some reason it ignores all single 4-digit numbers and takes only
those with a dash and number after. "Bob Phillips" wrote: Select the list, goto ToolsOptionsCustom Lists and hit the Import button. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... Sorry, I don't understand. This was only a sample list. We could be talking about 1000s of numbers. Connie "Bob Phillips" wrote: just enter those numbers with comma delimiters ToolsOptionsCustom List -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Sorry, I didn't see the entire post here until now. I tried the second
formula and that works, however, I may as well enter the numbers with a -0 to start with and then I don't need a helper column with a formula. If this is the best Excel can do with this type of sort, I guess that's what I will have to do. Really, I thought Excel could do better. Thank you. Connie "Roger Govier" wrote: Hi Connie You needed to change the formula to =IF(LEN(A2)=4,A2&"-1",A2) It will only add a "-1" to cells which are 4 characters in length. Are you saying that some cells have numbers like 12345? It worked fine for me and gave the correct sort order with your data sample. Perhaps a revision to =IF(ISNUMBER(FIND("-",A2)),A2,A2&"-0") This way it doesn't matter about the length of the numbers, if there is already a hyphen in the cell, it will repeat it as is, if not then add "-0" to the number, as there will not be any revisions of 0 in the list. Sort on this helper column, then delete the helper column, -- Regards Roger Govier "Connie Martin" wrote in message ... My spreadsheet data starts at A2, so I changed all the three A1's in your formula to A2 in the helper column, but what it does is simply add -1 to all my numbers. Also, some numbers have -2, -3 and so on. The way Excel sorts such a list by default is that 1382 would come before 1085-1. I don't want that. The -1, -2, etc., indicate a revision to the original document and indicate whether first, second, third revision, etc. Connie "Roger Govier" wrote: Hi Connie I can't figure a way to do with Custom Lists, but you could use a helper column. In the helper column enter =IF(LEN(A1)=4,A1&"-1",A1) and copy down Mark your data including the helper column, and sort by the helper column. After sorting, delete the helper column. -- Regards Roger Govier "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Connie
Bust the data into two columns by using datatext to columnsdelimited byotherhyphenfinish. Select both columns and sort by column A In Column C enter =IF(B2="",A2&B2,A2&"-"&B2) Copy down. Copy column C and paste specialvalueokesc. Delete A and B Try recording a macro so's you can do this again as you add more data. Gord Dibben MS Excel MVP On Thu, 25 Jan 2007 12:29:00 -0800, Connie Martin wrote: So I have to manually sort the list first?? I'm always adding numbers to this list. I don't think you have understood, or I have not explained well enough. Please see my response to Roger Govier. Thank you. Connie "Bob Phillips" wrote: Select the list, goto ToolsOptionsCustom Lists and hit the Import button. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... Sorry, I don't understand. This was only a sample list. We could be talking about 1000s of numbers. Connie "Bob Phillips" wrote: just enter those numbers with comma delimiters ToolsOptionsCustom List -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom List
Hi Connie
Really, I thought Excel could do better You are being a bit harsh. Excel is following absolutely normal rules for sorting. The first sort is correct for the data that you have entered. The second sort is correct for what is an entirely different data set. All my formula was doing was making up for the fact that all data was not entered in a consistent manner to start with. If you find it easier to enter the -0 with each data entry, then that's great. There is a standard pattern and Excel (or any other sorting routine) will get it correct. -- Regards Roger Govier "Connie Martin" wrote in message ... Sorry, I didn't see the entire post here until now. I tried the second formula and that works, however, I may as well enter the numbers with a -0 to start with and then I don't need a helper column with a formula. If this is the best Excel can do with this type of sort, I guess that's what I will have to do. Really, I thought Excel could do better. Thank you. Connie "Roger Govier" wrote: Hi Connie You needed to change the formula to =IF(LEN(A2)=4,A2&"-1",A2) It will only add a "-1" to cells which are 4 characters in length. Are you saying that some cells have numbers like 12345? It worked fine for me and gave the correct sort order with your data sample. Perhaps a revision to =IF(ISNUMBER(FIND("-",A2)),A2,A2&"-0") This way it doesn't matter about the length of the numbers, if there is already a hyphen in the cell, it will repeat it as is, if not then add "-0" to the number, as there will not be any revisions of 0 in the list. Sort on this helper column, then delete the helper column, -- Regards Roger Govier "Connie Martin" wrote in message ... My spreadsheet data starts at A2, so I changed all the three A1's in your formula to A2 in the helper column, but what it does is simply add -1 to all my numbers. Also, some numbers have -2, -3 and so on. The way Excel sorts such a list by default is that 1382 would come before 1085-1. I don't want that. The -1, -2, etc., indicate a revision to the original document and indicate whether first, second, third revision, etc. Connie "Roger Govier" wrote: Hi Connie I can't figure a way to do with Custom Lists, but you could use a helper column. In the helper column enter =IF(LEN(A1)=4,A1&"-1",A1) and copy down Mark your data including the helper column, and sort by the helper column. After sorting, delete the helper column. -- Regards Roger Govier "Connie Martin" wrote in message ... I want to create a new custom list in Tools/Options that will sort a list of numbers with dashes in this way: 1383 1769-7 1794-8 1849-1 1913 1957 1962-1 1977-3 1996-1 2009-2 2020-1 2051 2077-2 2079-2 2102-1 2121-1 2126-1 2129-1 2130-1 2135 2139 2161-2 2169-2 2179 2180 How do I have to set up my custom list so it sorts like this? Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced filter and a list | New Users to Excel | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
custom list does not sort | Excel Worksheet Functions | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Custom Views Dropdown List | Excel Discussion (Misc queries) |