Custom List

G

Guest

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
 
R

Roger Govier

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.
 
B

Bob Phillips

just enter those numbers with comma delimiters

Tools>Options>Custom List

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
G

Guest

Sorry, I don't understand. This was only a sample list. We could be talking
about 1000s of numbers. Connie
 
G

Guest

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
 
R

Roger Govier

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,
 
B

Bob Phillips

Select the list, goto Tools>Options>Custom Lists and hit the Import button.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
G

Guest

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.
 
G

Guest

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
 
G

Guest

Also, for some reason it ignores all single 4-digit numbers and takes only
those with a dash and number after.
 
G

Guest

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
 
G

Gord Dibben

Connie

Bust the data into two columns by using data>text to columns>delimited
by>other>hyphen>finish.

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 special>value>ok>esc.

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
 
R

Roger Govier

Hi Connie
Really, I thought Excel could do better
Click to expand...

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.
 
Top