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!

I would try recording a macro starting from the selection of the range of
cells to the sort order window, and then I would compare the code generated
by Excel with yours.

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

On Jul 24, 5:17 am, Dave Peterson <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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