How to Sort This Data

Printable View

Oct 11th, 2005, 01:10 AM
LadyAziera

How to Sort This Data
hello guys..

I have a problem in sorting the excel data in VB..This is example of the data:

PAP03-101-108-01-00-00-00-0000
PAP03-101-108-02-00-00-00-0000
PAP04-101-108-01-01-00-00-0000
PAP04-101-108-02-01-00-00-0000
PAP04-101-108-01-02-00-00-0000

When i try to sort the data, the result is:

PAP03-101-108-01-00-00-00-0000
PAP03-101-108-02-00-00-00-0000
PAP04-101-108-01-01-00-00-0000
PAP04-101-108-01-02-00-00-0000
PAP04-101-108-02-01-00-00-0000

Actually, Im trying to sort it to be in this way :

PAP03-101-108-01-00-00-00-0000
PAP04-101-108-01-01-00-00-0000
PAP04-101-108-01-02-00-00-0000
PAP03-101-108-02-00-00-00-0000
PAP04-101-108-02-01-00-00-0000

I donno how to do it.. I try to use macro code, but nothing happened.
VB Code:
```
Dim tmp1, tmp2 As String
 
     tmp1 = "B2"
     tmp2 = "D" & n
     objExcel.Range("tmp1:tmp2").Select
     objExcel.Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
```
help me guyz!
Oct 11th, 2005, 02:54 AM
Ecniv

Re: How to Sort This Data

It is sorting correctly according to text rules.
If you need to sort differently you'd need a couple of functions to split the cells data into other cells then sort as you require.
Oct 11th, 2005, 04:02 AM
LadyAziera

Re: How to Sort This Data

thanks 4 the reply!

Am i have to do it in excel form? Or it's better to split it first in the database juz before i display it in excel?
Oct 11th, 2005, 11:22 AM
Webtest

Re: How to Sort This Data

Would it be possible on the Excel Sheet to split "PAP03-101-108-02-00-00-00-0000" into TWO columns ... "PAP03-" in one, and "101-108-02-00-00-00-0000" in the second? If you can do that, things get a lot easier. You can format the first column Right Justified, and the second column Left Justified to make the data easier to read. Of course, someone is going to come along with some other weird format and foul things up!
Oct 14th, 2005, 01:59 AM
LadyAziera

Re: How to Sort This Data

thanks Webtest!

It works great!! :)
Oct 14th, 2005, 07:00 AM
Webtest

Re: How to Sort This Data

I was thinking that you might also find some advantage in forcing the right hand column into a number and formatting it:
Selection.NumberFormat = "0000-00-0000"
... but Excel only supports 15 significant digits, and you have 18. Oh well, so much for that brainstorm.
Oct 14th, 2005, 07:01 AM
Webtest

Re: How to Sort This Data

I was thinking that you might also find some advantage in forcing the right hand column into a number and formatting it 'Custom' (manually or programmatically):
Selection.NumberFormat = "000-000-00-00-00-00-0000"
... but Excel only supports 15 significant digits, and you have 18. Oh well, so much for that brainstorm.