How to autofill non-sequential cells with a list of names?

[tong7488]

Dear all, I'm trying to auto-populate a grid-like seating plan from a list of names. An example of the sequence is as followed:

[TABLE="width: 500"]
<tbody>[TR]
[TD]name 1[/TD]
[TD]name 5[/TD]
[TD]name 9[/TD]
[TD]name 13[/TD]
[/TR]
[TR]
[TD]name 2[/TD]
[TD]name 6[/TD]
[TD]name 10[/TD]
[TD]name 14[/TD]
[/TR]
[TR]
[TD]name 3[/TD]
[TD]name 7[/TD]
[TD]name 11[/TD]
[TD]name 15[/TD]
[/TR]
[TR]
[TD]name 4[/TD]
[TD]name 8[/TD]
[TD]name 12[/TD]
[TD]name 16[/TD]
[/TR]
</tbody>[/TABLE]

I've figured out how to define the range, but is only able to manually enter the names. Any help to understand how to do it is deeply appreciated!

 

[gallen]

Hello and welcome.

I'm not sure how well you know VBA. You also don't state if the names list is on a different sheet to the plan

Paste this code in to a code module. It just loops through the columns and then rows of seating plan range.

Let me know if it is too confusing. I've tried to comment as much as possible. You will need to alter the sheet names and written ranges accordingly.

Thanks

Sub SetSeatingPlan()


    Dim rNames As Range 'Range containing list of names. 
    Dim rPlan As Range 'Seating Plan which is B2:E5 for me on another sheet
    Dim c As Range ' used to loop through names
    
    Dim iRows As Integer 'number of rows in the plan
    Dim iColumns As Integer 'number of columns in the plan.
    
    Dim LastNameRow As Integer 'row that contains last name in list assuming nothing else is in column
    
    Dim i As Integer 'counter
    Dim x As Integer 'counter
    Dim y As Integer: y = 1 'counter
    
    'get last used row if it is dynamic. can hard write if value never changes
    LastNameRow = Sheets("Names").Range("A" & Sheets("Names").Rows.Count).End(xlUp).Row
    
    'set the ranges assuming first name is in cell A2
    Set rNames = Sheets("Names").Range("A2:A" & LastNameRow)
    Set rPlan = Sheets("Seating Plan").Range("B2:E5")
    
    'clear the seating plan
    rPlan.ClearContents
    
    'set the variables byu getting number of rows and columns
    iRows = rPlan.Rows.Count
    iColumns = rPlan.Columns.Count
    
    'Loop through columns and then rows and enter the names from the cells
    
    For i = 1 To iColumns
        For x = 1 To iRows
            'write the name in the cell
            rPlan.Cells(x, i) = rNames.Cells(y, 1)
            'if we have less names than seats then leave the loops
            If y >= rNames.Cells.Count Then GoTo ExitSub
            'got to next name
            y = y + 1
        Next x
    Next i
    
ExitSub:


End Sub

 

[tong7488]

Thanks for your reply. I'm afraid I'm not at all familiar with VBA.

Sorry I wasn't clearer before. I have 2 sheets in the workbook with 1 showing the 'names' in a column, in a particular order. The other is basically a table. Eg. 50 cloumns, 1st column for "name 1" to "name 20" , column 2 for "name 21" to "name 40" (Thus "name 1" would be adjacent to "name 21")

The thing is there's too many to enter manually (hundreds) and this is to be done yearly. So I'm exploring for a solution.

I know how to define the order of data entry through this link:

https://trumpexcel.com/enter-data-in-excel-in-specific-order/

But I can only enter the data manually. I can't find a way to auto fill the plan and keep it in the order I want. Hopefully this is a clearer explanation.

 

[gallen]

I can't open that link because of my work firewall.

If you can tell me the information highlighted in red I can make the code do as you wish:


So for example if you can tell me the names are on "a sheet called 'Names' and start from 'A2:A1001" and the seating plan is on a sheet named 'Seating Plan' in range 'A1:AX20'

 

[tong7488]

I can't open that link because of my work firewall.

If you can tell me the information highlighted in red I can make the code do as you wish:


So for example if you can tell me the names are on "a sheet called 'Names' and start from 'A2:A1001" and the seating plan is on a sheet named 'Seating Plan' in range 'A1:AX20'

Sheet1: 'Names' A2:A250

Sheet2: 'Seating plan' D2:O31


Thanks for your help!

Can this be done through Excel's Functions?

 

[Peter_SSs]

Welcome to the MrExcel board!

Your references and rows seem to be changing a bit, but given this...

Sheet1: 'Names' A2:A250

Sheet2: 'Seating plan' D2:O31

.. and you seem to want to fill the left column first, then 2nd from left etc ..

1st column for "name 1" to "name 20" , column 2 for "name 21" to "name 40" (Thus "name 1" would be adjacent to "name 21")

.. try this in cell D2 of Sheet2 then copy across to column O and down to row 31.

=IFERROR(INDEX(Sheet1!$A$2:$A$250,(COLUMNS($D2:D2)-1)*30+ROWS(D$2:D2)),"")

 

[tong7488]

Thanks Peter! This works and I've learnt something new!!

 

[Peter_SSs]

Thanks Peter! This works and I've learnt something new!!

You're welcome.