Sort Horizontally Then Vertically

joseph_r03 · Dec 14th 2007

Hello all

I have a distribution list of people to receive holiday greeting cards. In each cell is a customer's name, and their address below, like you would see on a postal letter. The range is 3 columns and around 150 rows - A1:C150. The problem is that I need these in alphabetical order going across and then down. So A1 first, then B1, C1, A2, B2, etc. I have attached an example showing original data and desired results.

A formula would be ideal, but a VBA solution would also be good.

advTHANKSance
joseph

Stanley D. Grom · Dec 15th 2007

Re: Sort Horizontally Then Vertically

joseph,

Here you go.

I duplicated all your raw data three more times:
John Doe
John Doe2
John Doe3
John Doe4

On the attached workbook, run the "ReSort" macro.

Have a great day,
Stan

jindon · Dec 15th 2007

Re: Sort Horizontally Then Vertically

try

Code

Sub test()
Dim a, e, b(), c(), i As Long, ii As Long, iii As Long, n As Long
With Selection
    a = .Value
    ReDim b(1 To .Cells.Count), c(1 To .Rows.Count, 1 To .Columns.Count)
    For Each e In a
        If Not IsEmpty(e) Then
            n = n + 1 : b(n) = e
        End If
    Next
    SortA b, 1, n
    iii = 1
    For i = 1 To n
        ii = IIf(i Mod (.Columns.Count + 1) = 0, 1, ii + 1)
        iii = IIf(i Mod (.Columns.Count + 1) = 0, iii + 1, iii)
        c(ii, iii) = b(i)
    Next
    .Value = b
End With
End Sub


Private Sub SortA(ary, LB, UB) 
Dim M As Variant, temp, i As Long, ii As Long 
i = UB : ii = LB 
M = ary(Int((Lb+UB)/2)) 
Do While ii <= i 
     Do While UCase(ary(ii)) < UCase(M)
          ii = ii + 1 
     Loop 
     Do While UCase(ary(i)) > UCase(M) 
          i = i - 1 
     Loop 
     If ii <= i Then 
          temp = ary(ii) : ary(ii) = ary(i) : ary(i) = temp 
          ii = ii + 1 : i = i - 1 
     End If 
Loop 
If LB < i Then SortA ary, LB, i 
If ii < UB Then SortA ary, ii, UB 
End Sub

Display More

s_u_resh · Dec 15th 2007

Re: Sort Horizontally Then Vertically

Dear joseph,

I would advise you store your data in separate worksheet like normal data. so you can sort as you required by excel.

Second, for printing as 3 columns like label format. You can use VBA code to print.
If you do not know VBA code then you can also use formulas and prepare report format.

Example - use Vlookup formula with the coresponding serial number.

1) aaaa address city
2) bbbbb address city
3) ccccc addess city

Report Fotmat

vlookup( 1 , range,no,0) , vlookup( 2 , range,no,0) , vlookup( 3 , range,no,0) ,

By doing this you can get the required format and also the required sorting type.

Hope this will help you.

Regards
Suresh

Mumbai.India

joseph_r03 · Dec 17th 2007

Re: Sort Horizontally Then Vertically

Thanks all for your replies.

I was able to successfully implement Stanley's solution first and that is the one I am using.

Sort Horizontally Then Vertically

Participate now!

Share