Sorting Data based on Dep Time and Reg Code using VBA

excellover2012 · Apr 11th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

So , it means , there is no duplicate Flight number and no same Dep Airport and Arr Airport , correct?

jindon · Apr 11th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Is it "run-time error '9'?

Isn't it "1004"?

If 1004, most probably a blank cell in col.A, otherwise I need to see the data.

I have no problem with your sample data.

excellover2012 · Apr 11th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

It is "run-time error '9'
Thanks.

jindon · Apr 11th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

I need to see the file, anyway.

excellover2012 · Apr 11th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

When I used the code on the real data, I got this error and I guess I know why, and that is because of the blank data at the end of the last record including the $ signs and the NIL sign in the blank row right after the real records.
[TABLE="width: 589"]

[tr]

[td]

MMM4537

[/td]

[td]

735

[/td]

[td]

945

[/td]

[td]

$OVI

[/td]

[td]

480

[/td]

[td]

LIMC

[/td]

[td]

LEMD

[/td]

[td]

23B

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[/TABLE]

Believe it or not, although the column A is blank at the end of the record but it seems that somehow the cursor stops at some blank columns.

When I removed all the blank columns and rows after the last record, your code worked perfect.

If the code can remove any blank columns and rows including the blank $ sign and the last NIL all the way down to let's say row 900, I believe it will work.
The blanks including the blank $ signs extend down some times to row 900.
Thank you.

jindon · Apr 11th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Try add one line

Code

For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp)) 
            If r.Value = "" Then Exit For    ' <- this line
            r.Value = Split(r.Value, "-")(0)

excellover2012 · Apr 12th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

This is working , Thank you very much Jindon , I will monitor it for couple of days and revert with the findings.

excellover2012 · Apr 28th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Hello Jindon, Sorry for late reply , I was on the go, but Thank you very much, it is working perfect, I really appreciate your kind help and patience.

jindon · Apr 29th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

You are welcome and thanks for the feedback.

excellover2012 · Apr 29th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Hello Jindon,

Agian facing sorting problem when the A2 is blank :

I was trying to sort the data by Flight# , but the result is that the Blanks that include $ comes on the top , then the data at the bottom , How can I do the sorting where by the blanks including the $ signs in the blank columns are ignored?

Thanks.

DataBase
[TABLE="width: 0"]

[tr]

[td]

Flight#

[/td]

[td]

Dep Time

[/td]

[td]

Arr Time

[/td]

[td]

Reg Code

[/td]

[td]

Pax Count

[/td]

[td]

Dep Airport

[/td]

[td]

Arr Airport

[/td]

[td]

Class

[/td]

[td]

Trip No.1

[/td]

[td]

Trip No.2

[/td]

[/tr]

[tr]

[td]

MMM8570

[/td]

[td]

900

[/td]

[td]

955

[/td]

[td]

$SSSL

[/td]

[td][/td]

[td]

EGLL

[/td]

[td]

EDDF

[/td]

[td]

XX80

[/td]

[td]

43000333

[/td]

[td]

43000333

[/td]

[/tr]

[tr]

[td]

MMM8570

[/td]

[td]

1600

[/td]

[td]

1655

[/td]

[td]

$SSSL

[/td]

[td][/td]

[td]

EDDF

[/td]

[td]

EGLL

[/td]

[td]

XX80

[/td]

[td]

43000333

[/td]

[td]

49000333

[/td]

[/tr]

[tr]

[td]

MMM6074

[/td]

[td]

40

[/td]

[td]

420

[/td]

[td]

$OVD

[/td]

[td]

480

[/td]

[td]

LIRF

[/td]

[td]

EGLL

[/td]

[td]

23B

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM7444

[/td]

[td]

45

[/td]

[td]

435

[/td]

[td]

$OVI

[/td]

[td]

446

[/td]

[td]

LEMD

[/td]

[td]

LIMC

[/td]

[td]

23B

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3773

[/td]

[td]

100

[/td]

[td]

350

[/td]

[td]

$XMX

[/td]

[td][/td]

[td]

EDDF

[/td]

[td]

EGLL

[/td]

[td]

22A

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM733

[/td]

[td]

120

[/td]

[td]

735

[/td]

[td]

$OXH

[/td]

[td]

285

[/td]

[td]

EDDK

[/td]

[td]

EDDF

[/td]

[td]

B70

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM4860

[/td]

[td]

511

[/td]

[td]

540

[/td]

[td]

$AVV

[/td]

[td][/td]

[td]

LIRR

[/td]

[td]

LIRR

[/td]

[td]

123

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM734

[/td]

[td]

600

[/td]

[td]

945

[/td]

[td]

$MMT

[/td]

[td]

433

[/td]

[td]

LIMC

[/td]

[td]

LIMJ

[/td]

[td]

22A

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM450

[/td]

[td]

615

[/td]

[td]

800

[/td]

[td]

$MMT

[/td]

[td]

424

[/td]

[td]

LIMJ

[/td]

[td]

LIMC

[/td]

[td]

22A

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3844

[/td]

[td]

650

[/td]

[td]

745

[/td]

[td]

$OXX

[/td]

[td][/td]

[td]

EDDF

[/td]

[td]

EGLL

[/td]

[td]

B70

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM4766

[/td]

[td]

720

[/td]

[td]

840

[/td]

[td]

$OVD

[/td]

[td]

480

[/td]

[td]

EGLL

[/td]

[td]

LIRF

[/td]

[td]

23B

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM4860

[/td]

[td]

730

[/td]

[td]

855

[/td]

[td]

$AVV

[/td]

[td]

465

[/td]

[td]

LIRR

[/td]

[td]

LIRF

[/td]

[td]

123

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM4537

[/td]

[td]

735

[/td]

[td]

945

[/td]

[td]

$OVI

[/td]

[td]

480

[/td]

[td]

LIMC

[/td]

[td]

LEMD

[/td]

[td]

23B

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

$

[/td]

[td][/td]

[/tr]

[/TABLE]

jindon · Apr 29th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Quote from excellover2012;606559

Hello Jindon,

Agian facing sorting problem when the A2 is blank :

Can you upload a file?

Picture doesn't help.

excellover2012 · Apr 29th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Thanks , Here is a sample.

jindon · Apr 29th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Change GetDups to

Code

Private Sub GetDups()
    Dim r As Range, w()
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp))
            If r.Value <> "" Then
                r.Value = Split(r.Value, "-")(0)
                If Not .exists(r.Value) Then
                    .Item(r.Value) = VBA.Array(r(, 6).Value, r(, 7).Value, r.Row, 64)
                Else
                    w = .Item(r.Value)
                    If r(, 6).Value = w(0) Then
                        If w(0) = w(1) Then
                            w(3) = w(3) + 1
                            Cells(w(2), 1).Value = _
                            Cells(w(2), 1).Value & "-" & Chr(w(3))
                            w(1) = 0
                            .Item(r.Value) = w
                        End If
                        If r(, 6).Value = r(, 7).Value Then
                            w(3) = w(3) + 1
                            .Item(r.Value) = w
                            r.Value = r.Value & "-" & Chr(w(3))
                        End If
                    End If
                End If
            End If
        Next
    End With
End Sub

Display More

excellover2012 · Apr 30th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Thank you, but what is the difference between the previous code and the new code.

I could not notice any difference between the two, what I was actually looking for is how to remove all the blank lines including the "$" signs and "nil" in those blank cells so that I can sort the data by Flight #, it's kind of separate code to only sort the data by Flight #.

excellover2012 · Apr 30th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

I am uploading the file with color code where the blank cells, if you try to sort it by Flight # , the $ sings first shows and then the data, I am trying to remove those blank cells altogether and sort them by Flight #.

jindon · Apr 30th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

try change in test

Code

With Range("a1").CurrentRegion

to

Code

With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 10)

excellover2012 · Apr 30th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Thank you,

See what happens when I try to sort the data by Flight #, it is sorting the blank cells including the $ and the blank nil and then at the end the database is sorted.

This is what I was trying to avoid , I want to sort the database by Flight # , but without the blank cells.

I am attaching a sample W/Sheet that has the codes.

jindon · Apr 30th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

Col.A of your file must contain invisible character.

How are you populating those cells?

jindon · Apr 30th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

So change you Macro4 (sorting macro) to

Code

Sub Macro4()
With Sheets("Sheet1")
    With .UsedRange.Columns(1)
        .Value = Evaluate("if(" & .Address & "<>"""",trim(clean(" _
        & .Address & ")),"""")")
    End With
    With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 10)
        .Sort .Range("a1"), 1
    End With
End With
End Sub

Display More

excellover2012 · Apr 30th 2012

Re: Sorting Data based on Dep Time and Reg Code using VB code

This is it Jindon, this does the trick , Thanks; I will try it on the real data and let you know.

Sorting Data based on Dep Time and Reg Code using VBA

Participate now!

Share