Data sorting problem
 

Excel 2003

I have a pivot table which draws data from a sheet in my workbook that is
constantly being updated. After I update the data, I update and sort the
pivot table with:

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.Range("K").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

Column K has data in it that looks like this before the sort:

22900400000F-GW
542A0612-13(L)S
2312120000000ME
etc€¦

After the sort of column K and performing some LEFT, MID and RIGHT functions
on the data, putting the results into columns A to D , some of the data in
column A looks like this:

229
230
2306
2309
231
2312
2313
233
etc€¦

I would like the data to be sorted like this:

229
230
231
233
..
542
542A
..
2306
2309
2312
2313
etc€¦

What change to the sort coding do I have to do to have the data in the order
I want?

TIA
David

Data sorting problem
 

Use the Value( ) function around the info in Col A to make them numbers
instead of text. They will then sort the way you want.
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Compass Rose" wrote:

Excel 2003

I have a pivot table which draws data from a sheet in my workbook that is
constantly being updated. After I update the data, I update and sort the
pivot table with:

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.Range("K").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

Column K has data in it that looks like this before the sort:

22900400000F-GW
542A0612-13(L)S
2312120000000ME
etc€¦

After the sort of column K and performing some LEFT, MID and RIGHT functions
on the data, putting the results into columns A to D , some of the data in
column A looks like this:

229
230
2306
2309
231
2312
2313
233
etc€¦

I would like the data to be sorted like this:

229
230
231
233
.
542
542A
.
2306
2309
2312
2313
etc€¦

What change to the sort coding do I have to do to have the data in the order
I want?

TIA
David

Data sorting problem
 

Gary, thanks for the response.

The problem is that column A isn't sorted - column K, which is one of the
columns in the pivot table, is the one that is sorted. Column A is populated
by applying a formula to all the rows in column K after it is sorted. The
formula that I use in column A is:
=IF(MID(K1,4,1)="0",LEFT(K1,3),LEFT(K1,4))

How can column K be sorted to give the results I'm looking for?

David

"Gary Brown" wrote:

Use the Value( ) function around the info in Col A to make them numbers
instead of text. They will then sort the way you want.
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Compass Rose" wrote:

Excel 2003

I have a pivot table which draws data from a sheet in my workbook that is
constantly being updated. After I update the data, I update and sort the
pivot table with:

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.Range("K").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

Column K has data in it that looks like this before the sort:

22900400000F-GW
542A0612-13(L)S
2312120000000ME
etc€¦

After the sort of column K and performing some LEFT, MID and RIGHT functions
on the data, putting the results into columns A to D , some of the data in
column A looks like this:

229
230
2306
2309
231
2312
2313
233
etc€¦

I would like the data to be sorted like this:

229
230
231
233
.
542
542A
.
2306
2309
2312
2313
etc€¦

What change to the sort coding do I have to do to have the data in the order
I want?

TIA
David