I am having a sorting problem, I have number and text data in a
column that is not sorting the way I would like it to. Here is an
example of my data

1+x
1
5
1
1
1
2
4
1
3
3(1)

However then I sort it top to bottom, here is what I get.
1
1
1
1
1
2
3
4
5
1+x
3(1)

Notice the 2 bottom items don't quite make it where I would like them
to (under the 1 and 3 respectively). Here is the chunk of code doing
the sort:

Dim SortRange As Range
Dim SortKey As Range

Set SortRange = TempList.Range("A21:K" & TempList.Cells(17,
12).Value - 1)
Set SortKey = TempList.Range(SortColumnAndRow)

SortRange.Sort Key1:=SortKey, _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

NOTE: SortColumnAndRow is a series of hard coded valid ranges.

I have tried to use xlSortTextAsNumbers as my 'DataOption1' but that
didn't work either.

Is there anything I can do to sort these properly? It is important to
keep in mind that this is column 7 of an 11 column sort.

Any help is appreciated!

On Jul 24, 5:17 am, Dave Peterson wrote:
xlSortTextAsNumbers won't treat 3(1) and 1+x as numbers. It will treat '123
with the apostrophe as a number.

I think you're going to have to separate your keys into another column and then
sort by that column.



theSquirrel wrote:

I am having a sorting problem, I have number and text data in a
column that is not sorting the way I would like it to. Here is an
example of my data

1+x
1
5
1
1
1
2
4
1
3
3(1)

However then I sort it top to bottom, here is what I get.
1
1
1
1
1
2
3
4
5
1+x
3(1)

Notice the 2 bottom items don't quite make it where I would like them
to (under the 1 and 3 respectively). Here is the chunk of code doing
the sort:

Dim SortRange As Range
Dim SortKey As Range

Set SortRange = TempList.Range("A21:K" & TempList.Cells(17,
12).Value - 1)
Set SortKey = TempList.Range(SortColumnAndRow)

SortRange.Sort Key1:=SortKey, _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

NOTE: SortColumnAndRow is a series of hard coded valid ranges.

I have tried to use xlSortTextAsNumbers as my 'DataOption1' but that
didn't work either.

Is there anything I can do to sort these properly? It is important to
keep in mind that this is column 7 of an 11 column sort.

Any help is appreciated!

--

Dave Peterson

bummer, i was afraid of that. I was going to try and avoid that, but
it looks like there is no way around it.

thanks for the answer Dave!

The alternative would be to treat everything as text

00001+x
00001
00003
00003(1)
00005

....

Either way can be pretty ugly.



theSquirrel wrote:

On Jul 24, 5:17 am, Dave Peterson wrote:
xlSortTextAsNumbers won't treat 3(1) and 1+x as numbers. It will treat '123
with the apostrophe as a number.

I think you're going to have to separate your keys into another column and then
sort by that column.



theSquirrel wrote:

I am having a sorting problem, I have number and text data in a
column that is not sorting the way I would like it to. Here is an
example of my data

1+x
1
5
1
1
1
2
4
1
3
3(1)

However then I sort it top to bottom, here is what I get.
1
1
1
1
1
2
3
4
5
1+x
3(1)

Notice the 2 bottom items don't quite make it where I would like them
to (under the 1 and 3 respectively). Here is the chunk of code doing
the sort:

Dim SortRange As Range
Dim SortKey As Range

Set SortRange = TempList.Range("A21:K" & TempList.Cells(17,
12).Value - 1)
Set SortKey = TempList.Range(SortColumnAndRow)

SortRange.Sort Key1:=SortKey, _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

NOTE: SortColumnAndRow is a series of hard coded valid ranges.

I have tried to use xlSortTextAsNumbers as my 'DataOption1' but that
didn't work either.

Is there anything I can do to sort these properly? It is important to
keep in mind that this is column 7 of an 11 column sort.

Any help is appreciated!

--

Dave Peterson

bummer, i was afraid of that. I was going to try and avoid that, but
it looks like there is no way around it.

thanks for the answer Dave!

--

Dave Peterson