Sorting Data based on Dep Time and Reg Code using VBA

excellover2012 · Apr 8th 2012

Hello ,

I will really appreciate any help in sorting the data using VB code with following parameters :-

By Dep Time provided the Reg Code follows the Dep Time sequence.

The earliest Dep Time first then continue the sequence.
[TABLE="width: 711"]

[tr]

[td]

" masterDB "

[/td]

[td][/td]

[/tr]

[tr]

[td]

Flight#

[/td]

[td]

Dep Time

[/td]

[td]

Arr Time

[/td]

[td]

Reg Code

[/td]

[td]

Count

[/td]

[td]

Dep Airport

[/td]

[td]

Arr Airport

[/td]

[td]

Class

[/td]

[td]

Trip Number

[/td]

[td]

Trip Number

[/td]

[/tr]

[tr]

[td]

MMM0813

[/td]

[td]

615

[/td]

[td]

815

[/td]

[td]

$AXS

[/td]

[td]

20

[/td]

[td]

WIII

[/td]

[td]

VHHH

[/td]

[td]

12U

[/td]

[td]

3194558

[/td]

[td]

3194558

[/td]

[/tr]

[tr]

[td]

MMM1805

[/td]

[td]

935

[/td]

[td]

1035

[/td]

[td]

$AX41

[/td]

[td]

50

[/td]

[td]

WSSS

[/td]

[td]

WMKK

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM0806

[/td]

[td]

1100

[/td]

[td]

1220

[/td]

[td]

$AX41

[/td]

[td]

10

[/td]

[td]

WMKK

[/td]

[td]

WIII

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM1695

[/td]

[td]

550

[/td]

[td]

730

[/td]

[td]

$AY15

[/td]

[td]

130

[/td]

[td]

WIII

[/td]

[td]

WIDP

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM1689

[/td]

[td]

910

[/td]

[td]

1100

[/td]

[td]

$AY15

[/td]

[td]

67

[/td]

[td]

WIDP

[/td]

[td]

WIII

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM0020

[/td]

[td]

30

[/td]

[td]

115

[/td]

[td]

$AMH

[/td]

[td][/td]

[td]

EGLL

[/td]

[td]

LFPG

[/td]

[td]

F7X

[/td]

[td]

3196664

[/td]

[td]

3196664

[/td]

[/tr]

[tr]

[td]

MMM0822

[/td]

[td]

230

[/td]

[td]

415

[/td]

[td]

$ARP

[/td]

[td][/td]

[td]

EDDF`

[/td]

[td]

EHAM

[/td]

[td]

F7X

[/td]

[td]

3274287

[/td]

[td]

3274287

[/td]

[/tr]

[tr]

[td]

MMM0736

[/td]

[td]

900

[/td]

[td]

1110

[/td]

[td]

$AQK

[/td]

[td][/td]

[td]

FAJS

[/td]

[td]

HAAB

[/td]

[td]

X-YZ

[/td]

[td]

3274352

[/td]

[td]

3274352

[/td]

[/tr]

[tr]

[td]

MMM0735

[/td]

[td]

1300

[/td]

[td]

1505

[/td]

[td]

$AQK

[/td]

[td][/td]

[td]

HAAB

[/td]

[td]

HECA

[/td]

[td]

529

[/td]

[td]

3274352

[/td]

[td]

3274352

[/td]

[/tr]

[tr]

[td]

MMM0568

[/td]

[td]

1700

[/td]

[td]

1855

[/td]

[td]

$AAO

[/td]

[td][/td]

[td]

HECA

[/td]

[td]

LGAT

[/td]

[td]

X1R

[/td]

[td]

3274392

[/td]

[td]

3274392

[/td]

[/tr]

[tr]

[td]

MMM0569

[/td]

[td]

1605

[/td]

[td]

1755

[/td]

[td]

$AAI

[/td]

[td][/td]

[td]

LGAT

[/td]

[td]

HECA

[/td]

[td]

M430

[/td]

[td]

3274392

[/td]

[td]

3274392

[/td]

[/tr]

[tr]

[td]

MMM0569

[/td]

[td]

1010

[/td]

[td]

1155

[/td]

[td]

$AAI

[/td]

[td][/td]

[td]

HECA

[/td]

[td]

LGAT

[/td]

[td]

9BC

[/td]

[td]

3274392

[/td]

[td]

3274392

[/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]

[tr]

[td][/td]

[/tr]

[/TABLE]

I want the sorting result to be :-

Result:-

[TABLE="width: 711"]

[tr]

[td]

Flight#

[/td]

[td]

Dep Time

[/td]

[td]

Arr Time

[/td]

[td]

Reg Code

[/td]

[td]

Count

[/td]

[td]

Dep Airport

[/td]

[td]

Arr Airport

[/td]

[td]

Class

[/td]

[td]

Trip Number

[/td]

[td]

Trip Number

[/td]

[/tr]

[tr]

[td]

MMM0020

[/td]

[td]

30

[/td]

[td]

115

[/td]

[td]

$AMH

[/td]

[td][/td]

[td]

EGLL

[/td]

[td]

LFPG

[/td]

[td]

F7X

[/td]

[td]

3196664

[/td]

[td]

3196664

[/td]

[/tr]

[tr]

[td]

MMM0822

[/td]

[td]

230

[/td]

[td]

415

[/td]

[td]

$ARP

[/td]

[td][/td]

[td]

EDDF`

[/td]

[td]

EHAM

[/td]

[td]

F7X

[/td]

[td]

3274287

[/td]

[td]

3274287

[/td]

[/tr]

[tr]

[td]

MMM1695

[/td]

[td]

550

[/td]

[td]

730

[/td]

[td]

$AY15

[/td]

[td]

130

[/td]

[td]

WIII

[/td]

[td]

WIDP

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM1689

[/td]

[td]

910

[/td]

[td]

1100

[/td]

[td]

$AY15

[/td]

[td]

67

[/td]

[td]

WIDP

[/td]

[td]

WIII

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM0813

[/td]

[td]

615

[/td]

[td]

815

[/td]

[td]

$AXS

[/td]

[td]

20

[/td]

[td]

WIII

[/td]

[td]

VHHH

[/td]

[td]

12U

[/td]

[td]

3194558

[/td]

[td]

3194558

[/td]

[/tr]

[tr]

[td]

MMM0736

[/td]

[td]

900

[/td]

[td]

1110

[/td]

[td]

$AQK

[/td]

[td][/td]

[td]

FAJS

[/td]

[td]

HAAB

[/td]

[td]

X-YZ

[/td]

[td]

3274352

[/td]

[td]

3274352

[/td]

[/tr]

[tr]

[td]

MMM0735

[/td]

[td]

1300

[/td]

[td]

1505

[/td]

[td]

$AQK

[/td]

[td][/td]

[td]

HAAB

[/td]

[td]

HECA

[/td]

[td]

529

[/td]

[td]

3274352

[/td]

[td]

3274352

[/td]

[/tr]

[tr]

[td]

MMM1805

[/td]

[td]

935

[/td]

[td]

1035

[/td]

[td]

$AX41

[/td]

[td]

50

[/td]

[td]

WSSS

[/td]

[td]

WMKK

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM0806

[/td]

[td]

1100

[/td]

[td]

1220

[/td]

[td]

$AX41

[/td]

[td]

10

[/td]

[td]

WMKK

[/td]

[td]

WIII

[/td]

[td]

A22

[/td]

[td]

3195643

[/td]

[td]

3195643

[/td]

[/tr]

[tr]

[td]

MMM0569

[/td]

[td]

1605

[/td]

[td]

1755

[/td]

[td]

$AAI

[/td]

[td][/td]

[td]

LGAT

[/td]

[td]

HECA

[/td]

[td]

M430

[/td]

[td]

3274392

[/td]

[td]

3274392

[/td]

[/tr]

[tr]

[td]

MMM0569

[/td]

[td]

1910

[/td]

[td]

2155

[/td]

[td]

$AAI

[/td]

[td][/td]

[td]

HECA

[/td]

[td]

LGAT

[/td]

[td]

9BC

[/td]

[td]

3274392

[/td]

[td]

3274392

[/td]

[/tr]

[tr]

[td]

MMM0568

[/td]

[td]

1700

[/td]

[td]

1855

[/td]

[td]

$AAO

[/td]

[td][/td]

[td]

HECA

[/td]

[td]

LGAT

[/td]

[td]

X1R

[/td]

[td]

3274392

[/td]

[td]

3274392

[/td]

[/tr]

[/TABLE]

Thank you.

jindon · Apr 8th 2012

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

See if this is how you wanted.

Code

Sub test()
Dim a, i As Long, ii As Long, w(), x As Object, n As Long
With Range("a1").CurrentRegion
    a = .Value
    With CreateObject("System.Collections.SortedList")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = "" Then Exit For
            If Not .Contains(a(i, 4)) Then
                ReDim w(1 To UBound(a, 2), 1 To 1)
            Else
                w = .Item(a(i, 4))
                ReDim Preserve w(1 To UBound(a, 2), 1 To UBound(w, 2) + 1)
            End If
            For ii = 1 To UBound(a, 2)
                w(ii, UBound(w, 2)) = a(i, ii)
            Next
            .Item(a(i, 4)) = w
        Next
        Set x = .Clone
    End With
    With CreateObject("System.Collections.SortedList")
        For i = 0 To x.Count - 1
            w = x.GetByIndex(i)
            .Item(w(2, 1)) = w
        Next
        Set x = .Clone
    End With
    With .Offset(, .Columns.Count + 1).Cells(2, 1)
        .Offset(-1).Resize(, UBound(a, 2)).Value = a
        For i = 0 To x.Count - 1
            w = x.GetByIndex(i)
            .Offset(n).Resize(UBound(w, 2), UBound(a, 2)).Value = _
            Application.Transpose(w)
            n = n + UBound(w, 2)
        Next
    End With
End With
Set x = Nothing
End Sub

Display More

excellover2012 · Apr 8th 2012

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

Thanks jindon,

I am getting run-time error 429

"ActiveX component can't create object"

The debug error is highlighted at the line :-

With CreateObject("System.Collections.SortedList")

jindon · Apr 8th 2012

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

Are you on Mac?

excellover2012 · Apr 8th 2012

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

No, I am on windows XP professional, Version 2002 , Service pack 3

jindon · Apr 8th 2012

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

How about

Code

Sub test()
Dim a, i As Long, ii As Long, w(), y, n As Long, temp
Dim iii As Long, iv As Long
With Range("a1").CurrentRegion
    a = .Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 2 To UBound(a, 1)
            If a(i, 1) = "" Then Exit For
            If Not .Exists(a(i, 4)) Then
                ReDim w(1 To UBound(a, 2), 1 To 1)
            Else
                w = .Item(a(i, 4))
                ReDim Preserve w(1 To UBound(a, 2), 1 To UBound(w, 2) + 1)
            End If
            For ii = 1 To UBound(a, 2)
                w(ii, UBound(w, 2)) = a(i, ii)
            Next
            If UBound(w, 2) > 1 Then
                For ii = 1 To UBound(w, 2) - 1
                    For iii = ii + 1 To UBound(w, 2)
                        If w(2, ii) > w(2, iii) Then
                            For iv = 1 To UBound(w, 1)
                                temp = w(iv, ii)
                                w(iv, ii) = w(iv, iii)
                                w(iv, iii) = temp
                            Next
                        End If
                    Next
                Next
            End If
            .Item(a(i, 4)) = w
        Next
        y = .items
    End With
    For i = 0 To UBound(y) - 1
        For ii = i + 1 To UBound(y)
            If y(i)(2, 1) > y(ii)(2, 1) Then
                temp = y(i)
                y(i) = y(ii)
                y(ii) = temp
            End If
        Next
    Next
    With .Offset(, .Columns.Count + 1).Cells(2, 1)
        .Offset(-1).Resize(, UBound(a, 2)).Value = a
        For i = 0 To UBound(y)
            w = y(i)
            .Offset(n).Resize(UBound(w, 2), UBound(a, 2)).Value = _
            Application.Transpose(w)
            n = n + UBound(w, 2)
        Next
    End With
End With
End Sub

Display More

Edited Code

excellover2012 · Apr 8th 2012

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

This is running good, but I need to check the data,

I will let you know.

Thank you.

jindon · Apr 8th 2012

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

I've just updated the code, so copy and try again.

So that the original table doesn't need to be sorted.

excellover2012 · Apr 8th 2012

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

The last record in the data base is not put in correct sequence

Data base

[TABLE="width: 647"]

[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]

Type

[/td]

[td]

PRNG

[/td]

[td]

New Seq

[/td]

[/tr]

[tr]

[td]

MMM823

[/td]

[TD="align: right"]920[/TD]
[TD="align: right"]1525[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

EBBR

[/td]

[td]

X2U

[/td]

[TD="align: right"]3728794[/TD]
[TD="align: right"]3728794[/TD]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]2215[/TD]
[TD="align: right"]525[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

VTBS

[/td]

[td]

EDDF

[/td]

[td]

X2U

[/td]

[TD="align: right"]3778712[/TD]
[TD="align: right"]3778712[/TD]

[/tr]

[tr]

[td]

MMM823

[/td]

[TD="align: right"]1735[/TD]
[TD="align: right"]1905[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

LOWW

[/td]

[td]

X2U

[/td]

[TD="align: right"]3780993[/TD]
[TD="align: right"]3780993[/TD]

[/tr]

[tr]

[td]

MMM824

[/td]

[TD="align: right"]2055[/TD]
[TD="align: right"]155[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

LOWW

[/td]

[td]

EDDF

[/td]

[td]

X2U

[/td]

[TD="align: right"]3780993[/TD]
[TD="align: right"]3780993[/TD]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]705[/TD]
[TD="align: right"]1515[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

ZGGG

[/td]

[td]

X2U

[/td]

[TD="align: right"]3783707[/TD]
[TD="align: right"]3783707[/TD]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]1725[/TD]
[TD="align: right"]2035[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

ZGGG

[/td]

[td]

VTBS

[/td]

[td]

X2U

[/td]

[TD="align: right"]3783707[/TD]
[TD="align: right"]3783707[/TD]

[/tr]

[tr]

[td]

MMM801

[/td]

[TD="align: right"]505[/TD]
[TD="align: right"]1310[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

KJFK

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM801

[/td]

[TD="align: right"]1510[/TD]
[TD="align: right"]1855[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

KJFK

[/td]

[td]

KIAH

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM800

[/td]

[TD="align: right"]2125[/TD]
[TD="align: right"]640[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

KIAH

[/td]

[td]

EBBR

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM833

[/td]

[TD="align: right"]220[/TD]
[TD="align: right"]835[/TD]

[td]

$77CC

[/td]

[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

EHAM

[/td]

[td]

65U

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM834

[/td]

[TD="align: right"]1605[/TD]
[TD="align: right"]2200[/TD]

[td]

$77CC

[/td]

[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

EHAM

[/td]

[td]

65U

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM853

[/td]

[TD="align: right"]235[/TD]
[TD="align: right"]410[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

OMDW

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM854

[/td]

[TD="align: right"]610[/TD]
[TD="align: right"]755[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

OMDW

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM884

[/td]

[TD="align: right"]1030[/TD]
[TD="align: right"]1855[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

VHHH

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM884

[/td]

[TD="align: right"]1155[/TD]
[TD="align: right"]2020[/TD]

[td]

$CXD

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

VHHH

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2055[/TD]
[TD="align: right"]555[/TD]

[td]

$OPEN

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2220[/TD]
[TD="align: right"]720[/TD]

[td]

$CXD

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM847

[/td]

[TD="align: right"]200[/TD]
[TD="align: right"]925[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

FAJS

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM847

[/td]

[TD="align: right"]1300[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

FAJS

[/td]

[td]

HKJK

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM848

[/td]

[TD="align: right"]2000[/TD]
[TD="align: right"]2359[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

HKJK

[/td]

[td]

EHAM

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM827

[/td]

[TD="align: right"]705[/TD]
[TD="align: right"]830[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

LIMC

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM828

[/td]

[TD="align: right"]1030[/TD]
[TD="align: right"]1600[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

LIMC

[/td]

[td]

EDDF

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM831

[/td]

[TD="align: right"]2100[/TD]
[TD="align: right"]305[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

EDDF

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]600[/TD]
[TD="align: right"]1000[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

VGHS

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]1130[/TD]
[TD="align: right"]1730[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

VGHS

[/td]

[td]

EDDF

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]1900[/TD]
[TD="align: right"]2000[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

LSGG

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[/TABLE]

[TABLE="width: 647"]

[tr]

[td]

Relsult

[/td]

[td][/td]

[/tr]

[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]

Type

[/td]

[td]

PRNG

[/td]

[td]

New Seq

[/td]

[/tr]

[tr]

[td]

MMM847

[/td]

[TD="align: right"]200[/TD]
[TD="align: right"]925[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

FAJS

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM847

[/td]

[TD="align: right"]1300[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

FAJS

[/td]

[td]

HKJK

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM848

[/td]

[TD="align: right"]2000[/TD]
[TD="align: right"]2359[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

HKJK

[/td]

[td]

EHAM

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM833

[/td]

[TD="align: right"]220[/TD]
[TD="align: right"]835[/TD]

[td]

$77CC

[/td]

[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

EHAM

[/td]

[td]

65U

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM834

[/td]

[TD="align: right"]1605[/TD]
[TD="align: right"]2200[/TD]

[td]

$77CC

[/td]

[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

EHAM

[/td]

[td]

65U

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM853

[/td]

[TD="align: right"]235[/TD]
[TD="align: right"]410[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

OMDW

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM854

[/td]

[TD="align: right"]610[/TD]
[TD="align: right"]755[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

OMDW

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM884

[/td]

[TD="align: right"]1030[/TD]
[TD="align: right"]1855[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

VHHH

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM801

[/td]

[TD="align: right"]505[/TD]
[TD="align: right"]1310[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

KJFK

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM801

[/td]

[TD="align: right"]1510[/TD]
[TD="align: right"]1855[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

KJFK

[/td]

[td]

KIAH

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM800

[/td]

[TD="align: right"]2125[/TD]
[TD="align: right"]640[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

KIAH

[/td]

[td]

EBBR

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]600[/TD]
[TD="align: right"]1000[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

VGHS

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]1130[/TD]
[TD="align: right"]1730[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

VGHS

[/td]

[td]

EDDF

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]1900[/TD]
[TD="align: right"]2000[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

LSGG

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM827

[/td]

[TD="align: right"]705[/TD]
[TD="align: right"]830[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

LIMC

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM828

[/td]

[TD="align: right"]1030[/TD]
[TD="align: right"]1600[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

LIMC

[/td]

[td]

EDDF

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM831

[/td]

[TD="align: right"]2100[/TD]
[TD="align: right"]305[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

EDDF

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM823

[/td]

[TD="align: right"]920[/TD]
[TD="align: right"]1525[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

EBBR

[/td]

[td]

X2U

[/td]

[TD="align: right"]3728794[/TD]
[TD="align: right"]3728794[/TD]

[/tr]

[tr]

[td]

MMM823

[/td]

[TD="align: right"]1735[/TD]
[TD="align: right"]1905[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

LOWW

[/td]

[td]

X2U

[/td]

[TD="align: right"]3780993[/TD]
[TD="align: right"]3780993[/TD]

[/tr]

[tr]

[td]

MMM824

[/td]

[TD="align: right"]2055[/TD]
[TD="align: right"]155[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

LOWW

[/td]

[td]

EDDF

[/td]

[td]

X2U

[/td]

[TD="align: right"]3780993[/TD]
[TD="align: right"]3780993[/TD]

[/tr]

[tr]

[td]

MMM884

[/td]

[TD="align: right"]1155[/TD]
[TD="align: right"]2020[/TD]

[td]

$CXD

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

VHHH

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2220[/TD]
[TD="align: right"]720[/TD]

[td]

$CXD

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2055[/TD]
[TD="align: right"]555[/TD]

[td]

$OPEN

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]2215[/TD]
[TD="align: right"]525[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

VTBS

[/td]

[td]

EDDF

[/td]

[td]

X2U

[/td]

[TD="align: right"]3778712[/TD]
[TD="align: right"]3778712[/TD]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]705[/TD]
[TD="align: right"]1515[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

ZGGG

[/td]

[td]

X2U

[/td]

[TD="align: right"]3783707[/TD]
[TD="align: right"]3783707[/TD]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]1725[/TD]
[TD="align: right"]2035[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

ZGGG

[/td]

[td]

VTBS

[/td]

[td]

X2U

[/td]

[TD="align: right"]3783707[/TD]
[TD="align: right"]3783707[/TD]

[/tr]

[/TABLE]

2 Issues :-

1) Notice the Flight # MMM878
The first record shold have been for Dep Time 707 , then 1725 then 2215 and then placed in the sequence right before or after the Flight # MMM827/MMM828/MMM831

2) Flight # MMM885
[TABLE="width: 647"]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2055[/TD]
[TD="align: right"]555[/TD]

[td]

$OPEN

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[/TABLE]

Should be before the Flight #
[TABLE="width: 647"]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2220[/TD]
[TD="align: right"]720[/TD]

[td]

$CXD

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[/TABLE]

This should be the correct sort sequence ;-

[TABLE="width: 647"]

[tr]

[td]

Relsult

[/td]

[td][/td]

[/tr]

[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]

Type

[/td]

[td]

PRNG

[/td]

[td]

New Seq

[/td]

[/tr]

[tr]

[td]

MMM847

[/td]

[TD="align: right"]200[/TD]
[TD="align: right"]925[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

FAJS

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM847

[/td]

[TD="align: right"]1300[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

FAJS

[/td]

[td]

HKJK

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM848

[/td]

[TD="align: right"]2000[/TD]
[TD="align: right"]2359[/TD]
[TD="align: right"]7774[/TD]
[TD="align: right"]0[/TD]

[td]

HKJK

[/td]

[td]

EHAM

[/td]

[td]

82L

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM833

[/td]

[TD="align: right"]220[/TD]
[TD="align: right"]835[/TD]

[td]

$77CC

[/td]

[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

EHAM

[/td]

[td]

65U

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM834

[/td]

[TD="align: right"]1605[/TD]
[TD="align: right"]2200[/TD]

[td]

$77CC

[/td]

[TD="align: right"]0[/TD]

[td]

EHAM

[/td]

[td]

EHAM

[/td]

[td]

65U

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM853

[/td]

[TD="align: right"]235[/TD]
[TD="align: right"]410[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

OMDW

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM854

[/td]

[TD="align: right"]610[/TD]
[TD="align: right"]755[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

OMDW

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM884

[/td]

[TD="align: right"]1030[/TD]
[TD="align: right"]1855[/TD]

[td]

$CXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

VHHH

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM801

[/td]

[TD="align: right"]505[/TD]
[TD="align: right"]1310[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

KJFK

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM801

[/td]

[TD="align: right"]1510[/TD]
[TD="align: right"]1855[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

KJFK

[/td]

[td]

KIAH

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM800

[/td]

[TD="align: right"]2125[/TD]
[TD="align: right"]640[/TD]

[td]

$88VV

[/td]

[TD="align: right"]0[/TD]

[td]

KIAH

[/td]

[td]

EBBR

[/td]

[td]

12Y

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]600[/TD]
[TD="align: right"]1000[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

VGHS

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]1130[/TD]
[TD="align: right"]1730[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

VGHS

[/td]

[td]

EDDF

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM3883

[/td]

[TD="align: right"]1900[/TD]
[TD="align: right"]2000[/TD]

[td]

$99CB

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

LSGG

[/td]

[td]

99V

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]705[/TD]
[TD="align: right"]1515[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

ZGGG

[/td]

[td]

X2U

[/td]

[TD="align: right"]3783707[/TD]
[TD="align: right"]3783707[/TD]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]1725[/TD]
[TD="align: right"]2035[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

ZGGG

[/td]

[td]

VTBS

[/td]

[td]

X2U

[/td]

[TD="align: right"]3783707[/TD]
[TD="align: right"]3783707[/TD]

[/tr]

[tr]

[td]

MMM878

[/td]

[TD="align: right"]2215[/TD]
[TD="align: right"]525[/TD]

[td]

$AXX

[/td]

[TD="align: right"]0[/TD]

[td]

VTBS

[/td]

[td]

EDDF

[/td]

[td]

X2U

[/td]

[TD="align: right"]3778712[/TD]
[TD="align: right"]3778712[/TD]

[/tr]

[tr]

[td]

MMM827

[/td]

[TD="align: right"]705[/TD]
[TD="align: right"]830[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

LIMC

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM828

[/td]

[TD="align: right"]1030[/TD]
[TD="align: right"]1600[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

LIMC

[/td]

[td]

EDDF

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM831

[/td]

[TD="align: right"]2100[/TD]
[TD="align: right"]305[/TD]

[td]

$CCB

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

EDDF

[/td]

[td]

2UM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM823

[/td]

[TD="align: right"]920[/TD]
[TD="align: right"]1525[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

EBBR

[/td]

[td]

X2U

[/td]

[TD="align: right"]3728794[/TD]
[TD="align: right"]3728794[/TD]

[/tr]

[tr]

[td]

MMM823

[/td]

[TD="align: right"]1735[/TD]
[TD="align: right"]1905[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

EBBR

[/td]

[td]

LOWW

[/td]

[td]

X2U

[/td]

[TD="align: right"]3780993[/TD]
[TD="align: right"]3780993[/TD]

[/tr]

[tr]

[td]

MMM824

[/td]

[TD="align: right"]2055[/TD]
[TD="align: right"]155[/TD]

[td]

$AXC

[/td]

[TD="align: right"]0[/TD]

[td]

LOWW

[/td]

[td]

EDDF

[/td]

[td]

X2U

[/td]

[TD="align: right"]3780993[/TD]
[TD="align: right"]3780993[/TD]

[/tr]

[tr]

[td]

MMM884

[/td]

[TD="align: right"]1155[/TD]
[TD="align: right"]2020[/TD]

[td]

$CXD

[/td]

[TD="align: right"]0[/TD]

[td]

EDDF

[/td]

[td]

VHHH

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2055[/TD]
[TD="align: right"]555[/TD]

[td]

$OPEN

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2220[/TD]
[TD="align: right"]720[/TD]

[td]

$CXD

[/td]

[TD="align: right"]0[/TD]

[td]

VHHH

[/td]

[td]

EDDF

[/td]

[td]

5MM

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[/TABLE]

Thank you.

jindon · Apr 8th 2012

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

Can you upload a workbook?

Posting picture doesn't help much.

jindon · Apr 8th 2012

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

My interpretation is:

1) Group by Reg Code
2) Sort each group by Dep time
3) Sort entire group by earliest dep time in each group.

Quote

2) Flight # MMM885
MMM885 2055 555 $OPEN 0 VHHH EDDF 5MM NIL NIL

Should be before the Flight #
MMM885 2220 720 $CXD 0 VHHH EDDF 5MM NIL NIL

MMM885 2220 720 $CXD 0 VHHH EDDF 5MM NIL NIL
is a member of $CXD
[TABLE="width: 288"]

[tr]

[TD="width: 72"]MMM884[/TD]
[TD="width: 72, align: right"]1155[/TD]
[TD="width: 72, align: right"]2020[/TD]
[TD="width: 72"] $CXD[/TD]

[/tr]

[tr]

[td]

MMM885

[/td]

[TD="align: right"]2220[/TD]
[TD="align: right"]720[/TD]

[td]

$CXD

[/td]

[/tr]

[/TABLE]

So they are correct order for me.

excellover2012 · Apr 8th 2012

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

Hello jindon,

Sorry for late reply, I did some more testing and it seems the code is doing just what it is supposed to , Thank you, I will keep testing it , but the result of the sort is coming adjacent to the database, I need the same data to be sorted and not display it adjacent to the database.

Thanks.

jindon · Apr 8th 2012

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

Change

Code

With .Offset(, .Columns.Count + 1).Cells(2, 1)
             .Offset(-1).Resize(, UBound(a, 2)).Value = a 
            For i = 0 To UBound(y) 
                w = y(i) 
                .Offset(n).Resize(UBound(w, 2), UBound(a, 2)).Value = _ 
                Application.Transpose(w) 
                n = n + UBound(w, 2) 
            Next 
        End With

to

Code

n = 2
        For i = 0 To UBound(y)
            w = y(i)
            .Rows(n).Resize(UBound(w, 2)).Value = _
            Application.Transpose(w)
            n = n + UBound(w, 2)
        Next

excellover2012 · Apr 11th 2012

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

Hello Jindon,

First and foremost , please let me thank you for your kind help, I have been testing the extracted data and applied your code which worked perfectly, but I noticed in the updated database extracted that there are data that come as duplicate due to changes to them when a new extraction is done :-

[TABLE="width: 589"]

[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]

Notice MM4860 is duplicated twice, but one record has the same Dep and Arr Airports

[TABLE="width: 505"]

[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]

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]

MMM4860

[/td]

[td]

730

[/td]

[td]

855

[/td]

[td]

$AVV

[/td]

[td]

465

[/td]

[td]

LIRR

[/td]

[td]

EDDF

[/td]

[td]

123

[/td]

[td]

NIL

[/td]

[td]

NIL

[/td]

[/tr]

[/TABLE]

I need a VB code to add a letter A after the flight number if the record in the data base has same flight number and same Dep and Arr Airports.
E.g
[TABLE="width: 505"]

[tr]

[td]

MMM4860A

[/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]

[/TABLE]

On other hand, Notice Flight numbers MMM8570,MMM3773 and MMM3844 has the same Dep and Arr Airports but different flight numbers, this is OK.
e.g
[TABLE="width: 639"]

[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]

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]

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]

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]

[/TABLE]

In summary, If the database has duplicate Flight# and same Dep Airport and Arr Airport , it should add a suffix A after the same Flight# that has the same Dep Airport and Arr Airport.
Thank you again for your help.

jindon · Apr 11th 2012

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

Is ther any possibilty to have more than 2 dups for that condition?

excellover2012 · Apr 11th 2012

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

Yes, it is possible.

jindon · Apr 11th 2012

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

try

Code

Sub test()
    Dim a, i As Long, ii As Long, w(), y, n As Long, temp
    Dim iii As Long, iv As Long
    GetDups
    With Range("a1").CurrentRegion
        a = .Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(a, 1)
                If a(i, 1) = "" Then Exit For
                If Not .exists(a(i, 4)) Then
                    ReDim w(1 To UBound(a, 2), 1 To 1)
                Else
                    w = .Item(a(i, 4))
                    ReDim Preserve w(1 To UBound(a, 2), 1 To UBound(w, 2) + 1)
                End If
                For ii = 1 To UBound(a, 2)
                    w(ii, UBound(w, 2)) = a(i, ii)
                Next
                If UBound(w, 2) > 1 Then
                    For ii = 1 To UBound(w, 2) - 1
                        For iii = ii + 1 To UBound(w, 2)
                            If w(2, ii) > w(2, iii) Then
                                For iv = 1 To UBound(w, 1)
                                    temp = w(iv, ii)
                                    w(iv, ii) = w(iv, iii)
                                    w(iv, iii) = temp
                                Next
                            End If
                        Next
                    Next
                End If
                .Item(a(i, 4)) = w
            Next
            y = .items
        End With
        For i = 0 To UBound(y) - 1
            For ii = i + 1 To UBound(y)
                If y(i)(2, 1) > y(ii)(2, 1) Then
                    temp = y(i)
                    y(i) = y(ii)
                    y(ii) = temp
                End If
            Next
        Next
        n = 2
        For i = 0 To UBound(y)
        w = y(i)
        .Rows(n).Resize(UBound(w, 2)).Value = _
        Application.Transpose(w)
        n = n + UBound(w, 2)
        Next
    End With
End Sub




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))
        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
    Next
End With
End Sub

Display More

excellover2012 · Apr 11th 2012

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

Thanks, I will run some test tonight and let you know.

excellover2012 · Apr 11th 2012

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

I am getting run-time error '9'
SubScript out of range ,

and the error is highlighted on the line :-

r.Value = Split(r.Value, "-")(0)

Thanks.

jindon · Apr 11th 2012

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

That means you have BLANK in col.A

Sorting Data based on Dep Time and Reg Code using VBA

Participate now!

Share

Users Viewing This Thread