Distribution of list to columns

[Coreyflashmiller]

I have a list in a2:a240. The amount of names in that list vary from 8 to 200. I need to take that list and evenly distribute the list into 8 columns in d2 thru k2. Example have 27 names in list I need to split it into 8 parts which would be 3 columns of 4 names and 5 columns of 3. Since I am pre sorting the list before hand I allways want the script to weight it toward the front. So example compared to 27 and let's say 26 names u now would get 2 columns of 4 and 6 columns of 3.

Any help ?

 

[B___P]

Name 1			Name 1	Name 2	Name 3	Name 4	Name 5	Name 6	Name 7	Name 8
Name 2			Name 9	Name 10	Name 11	Name 12	Name 13	Name 14	Name 15	Name 16
Name 3			Name 17	Name 18	Name 19	Name 20	Name 21	Name 22	Name 23
Name 4
Name 5
Name 6
Name 7
Name 8
Name 9
Name 10
Name 11
Name 12
Name 13
Name 14
Name 15
Name 16
Name 17
Name 18
Name 19
Name 20
Name 21
Name 22
Name 23

This is my output with excel 2010. May be something has changed in 365. Anyway I wrote this workaround

Sub Coreyflashmiller_25_05_2021_2()
Dim idx As Long
Dim MyCell As Range
Dim Rpos As Long
Dim Cpos As Long

idx = 0
    Range("D2:K31").ClearContents

For Each MyCell In Range("a2:a241") 'one more row...
    idx = idx + 1
    Rpos = Round(idx / 8 + IIf(idx Mod 8, 0.5, 0), 0) - 1
    Cpos = 1 + (idx - 1) Mod 8
    [D2].Offset(Rpos, Cpos - 1) = MyCell.Value
Next MyCell

End Sub

Hope this helps

 

[Coreyflashmiller]

Name 1			Name 1	Name 2	Name 3	Name 4	Name 5	Name 6	Name 7	Name 8
Name 2			Name 9	Name 10	Name 11	Name 12	Name 13	Name 14	Name 15	Name 16
Name 3			Name 17	Name 18	Name 19	Name 20	Name 21	Name 22	Name 23
Name 4
Name 5
Name 6
Name 7
Name 8
Name 9
Name 10
Name 11
Name 12
Name 13
Name 14
Name 15
Name 16
Name 17
Name 18
Name 19
Name 20
Name 21
Name 22
Name 23

This is my output with excel 2010. May be something has changed in 365. Anyway I wrote this workaround

Sub Coreyflashmiller_25_05_2021_2()
Dim idx As Long
Dim MyCell As Range
Dim Rpos As Long
Dim Cpos As Long

idx = 0
    Range("D2:K31").ClearContents

For Each MyCell In Range("a2:a241") 'one more row...
    idx = idx + 1
    Rpos = Round(idx / 8 + IIf(idx Mod 8, 0.5, 0), 0) - 1
    Cpos = 1 + (idx - 1) Mod 8
    [D2].Offset(Rpos, Cpos - 1) = MyCell.Value
Next MyCell

End Sub

Hope this helps

Just to clarify name 1 2 3 should be in first column then 4 5 6 in next and so forth

 

[Coreyflashmiller]

Just to clarify name 1 2 3 should be in first column then 4 5 6 in next and so forth

This code does work for what you showed except I am trying to column 1 has 123 column 2 has 456 .... the reason I need it that way is cause before I split it into columns I sort the list based on a value next to name large to small.

 

[B___P]

Did I guess?

Sub Coreyflashmiller_25_05_2021_3()
Dim idx As Long
Dim x As Long
Dim y As Long
Dim MyRows As Long
Dim MyCols As Long

Range("D2:K31").ClearContents
idx = 0
MyRows = Round((WorksheetFunction.CountA(Range("a2:a240")) / 8 + 0.5), 0)
MyCols = WorksheetFunction.CountA(Range("a2:a240")) Mod 8

For y = 0 To MyCols - 1
    For x = 0 To MyRows - 1
        idx = idx + 1
        [D2].Offset(x, y) = Cells(1 + idx, 1).Value
    Next x
Next y

For y = MyCols To 8 - 1
    For x = 0 To MyRows - 2
        idx = idx + 1
        [D2].Offset(x, y) = Cells(1 + idx, 1).Value
    Next x
Next y

End Sub

 

[navic]

this is what i am trying to do but for some reason it wont spread across all eight columns evenly like your example

Based on your example from the picture in the '#9' topic

Formula in the 'D2' cell (copy across)

=IFERROR(INDEX($A$2:$A$20,ROW(C1)+(8*(COLUMNS($D$2:D$2)-1))),"")

Note: '8*' indicates how many rows you want

 

[navic]

After some observation of your pictorial example here is one more example

8 rows (image 4)

=IFERROR(INDEX($A$2:$A$20,ROW(C1)+(8*(COLUMNS($D$2:D$2)-1))),"")

5 rows (image 4a)

=IFERROR(INDEX($A$2:$A$20,ROW(A1)+(5*(COLUMNS($D$2:D$2)-1))),"")

 

[Coreyflashmiller]

Based on your example from the picture in the '#9' topic

Formula in the 'D2' cell (copy across)

=IFERROR(INDEX($A$2:$A$20,ROW(C1)+(8*(COLUMNS($D$2:D$2)-1))),"")

Note: '8*' indicates how many rows you want

i get what your saying but the issue is i dont know the row amount i only know the column amount for sure needs to be 8 columns the rows needs to max out at 25. but essentially i dont wat it to fill all 25 rows unless there was that many in the list.

so my range to fill is d2:k27 i need it to evenly distrubute the list into the columns. like some have screenshot , hopefully that make sense

 

[Coreyflashmiller]

so if there was a list of 19 names this is how i would like it to come out. cause one time i run this there could be 20 names next time 200 either way i need to evenly distubute the names like the picture

 

[Coreyflashmiller]

Did I guess?

Sub Coreyflashmiller_25_05_2021_3()
Dim idx As Long
Dim x As Long
Dim y As Long
Dim MyRows As Long
Dim MyCols As Long

Range("D2:K31").ClearContents
idx = 0
MyRows = Round((WorksheetFunction.CountA(Range("a2:a240")) / 8 + 0.5), 0)
MyCols = WorksheetFunction.CountA(Range("a2:a240")) Mod 8

For y = 0 To MyCols - 1
    For x = 0 To MyRows - 1
        idx = idx + 1
        [D2].Offset(x, y) = Cells(1 + idx, 1).Value
    Next x
Next y

For y = MyCols To 8 - 1
    For x = 0 To MyRows - 2
        idx = idx + 1
        [D2].Offset(x, y) = Cells(1 + idx, 1).Value
    Next x
Next y

End Sub

DUDE this totally works ..... omg you have helped so much thank you