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

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

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

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

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

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

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

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

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

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

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

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

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