Excel sorting conundrum

[Fkacn_tt]

I am trying to sort 2 adjacent columns with data in this order:

item2   item2sub1
            item2sub3
            item2sub2
            item2sub4
item1   item1sub3
            item1sub1
            item1sub2
item3   item3sub4
            item3sub2
            item3sub1
            item3sub3

whenever i sort this menu it keeps messing up the order between the item# and its corresponding item#sub#.

any help would be greatly appreciated in sorting this data.
P.S. the actual data is alphabetical and would be sorted alphabetically in ascending order.

EG.

item1   item1sub1
            item1sub2
            item1sub3
            item1sub4
item2   item2sub1
            item2sub2
            item2sub3
item3   item3sub1
            item3sub2
            item3sub3
            item3sub4

the item#sub# sorting is not a priority just the item# anything extra would be welcomed.

[phoenix31tt]

in excel you would have to enable macros and write a vba script to do something like that...

it can be done of the bat with something like access doh...

how important is this to you? cuz u could also just do it the long way and manually sort the first column then sort the second column by selecting the different groups

[Fkacn_tt]

ill try the macros way it that dose not work, ill try the manual method.

[phoenix31tt]

If you familiar with VBA coding i found the following script which may help.. you would need to modify it however

It needs a sheet called "temp", and requires that every section be spaced by iRowsPerJob rows (see code).

Code: [Select]

Sub SortJobs()
    Dim iR As Integer, jR As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim sFirstAdrs As String
    Dim rFound As Range
    Dim rCopy As Range
    Dim nJobs As Long
    Const iRowsPerJob As Integer = 13
       
    Set ws1 = ThisWorkbook.Worksheets(1)
    Set ws2 = ThisWorkbook.Sheets("temp")
    ws2.Cells.Clear
   
    ' copy the job numbers to "temp"
    With ws1.Range("A:A")
        Set rFound = .Find( _
What:="Job #:", _
                           After:=ws1.Range("a1"), _
                           LookIn:=xlValues)
        sFirstAdrs = rFound.Address
        Do
            jR = jR + 1
            ws2.Cells(jR, 1) = rFound.Offset(, 1) ' job #
            Set rFound = .FindNext(rFound)
        Loop While Not rFound Is Nothing And rFound.Address <> sFirstAdrs
    End With
   
    ' sort by job number
    ws2.UsedRange.Sort Key1:=ws2.Cells(1, 1), Order1:=xlAscending, _
                       Orientation:=xlSortColumns, Header:=xlNo
   
    ' cut the jobs in order to the bottom of the list
    Application.EnableEvents = False
   
    nJobs = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    Set rCopy = ws1.Range(sFirstAdrs).Offset(iRowsPerJob * nJobs)
   
    For iR = 1 To nJobs
        ' the two-column range is necessary because of the merged cells
        Set rFound = ws1.Range("B:C").Find( _
What:=ws2.Cells(iR, 1), _
                                           LookIn:=xlFormulas, _
                                           After:=ws1.Range("B1"))
        ws1.Cells(rFound.Row, 1).Resize(iRowsPerJob).EntireRow.Cut
        rCopy.Insert shift:=xlDown
    Next
    Application.EnableEvents = True
                       
End Sub
Gluck