Split column of data into 3 columns...

[DME]

Hi all, I'm assuming this will require VBA but please let me know if this is best posted under another Forum category.

I have a long list of names in Excel (1,000+) in Column A. I need to organize them alphabetically (A to Z), but as follows:

Column B Column C Column D
1st Name 2nd Name 3rd Name
4th Name 5th Name 6th Name
7th Name 8th Name 9th Name
etc.....

Is there any easy way to do this? The rationale for setting up the names this way is readability when printed (not my decision - direction given to me that I cannot change). I've tried googling this in several different ways but can't find anything. Hoping someone has some thoughts!

Thanks in advance,
DME

 

[vletm]

DME
You could do it with this...

Sub Do_DME()
    Application.ScreenUpdating = False
    With ActiveSheet
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A:A")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        a = 1
        b = 1
        c = 2
        Do
            .Cells(b, c) = .Cells(a, 1)
            c = c + 1
            If c > 4 Then
                c = 2
                b = b + 1
            End If
            a = a + 1
        Loop Until .Cells(a, 1) = Empty
    End With
End Sub

... and some features depends of Your file.

 

[DME]

@vletm Amazing! That worked perfectly. Thank you so much! One question though - is there a way to do this and retain any formatting from the original list in Column A? A few names are bolded / cells are filled with a color - any way to keep that? Thanks again

 

[vletm]

DME
change row
.Cells(b, c) = .Cells(a, 1)
to
.Cells(a, 1).Copy Destination:=.Cells(b, c)

 

[DME]

@vletm Perfect, thank you again!!