need help with excel function! trying to create seating chart

Posted on 2012-08-28
Last Modified: 2013-01-22
hello all looking to create a seating chart for a meeting we have coming up
basically i have all the users names in row B of my excel sheet. their table number for the meeting is in row I
there are 10 users to a table
in another sheet i want to be able to list the users under their appropriate table number
across the 1st row of the sheet (the top row) i have the table numbers, and below each table number i want to list all the users that are going to be seated at that table
is there a function i can use (i was thinking, vlookup or index) that would allow me to list the users under their appropriate table number, based on the value that is in row I from the sheet?
Question by:jsctechy
    LVL 10

    Expert Comment

    This Visual Basic routine will do the trick; it creates the seating chart in a new worksheet:
    Sub MakeTables()
    Dim fromRow As Long
    Dim fromSheet As Worksheet
    Dim nTables As Integer
    Dim toRow() As Long
    Dim toSheet As Worksheet
    Dim theTable As Long
        Set fromSheet = ActiveSheet
        Sheets.Add After:=Sheets(Sheets.Count)
        Set toSheet = ActiveSheet
        fromRow = 2
        nTables = 0
        Do While Not IsEmpty(fromSheet.Cells(fromRow, 2).Value)
            If nTables < fromSheet.Cells(fromRow, 9).Value Then
                nTables = fromSheet.Cells(fromRow, 9).Value
            End If
            fromRow = fromRow + 1
        ReDim toRow(nTables)
        fromRow = 2
        Do While Not IsEmpty(fromSheet.Cells(fromRow, 2).Value)
            theTable = fromSheet.Cells(fromRow, 9).Value
            If toRow(theTable) = 0 Then
                toSheet.Cells(1, theTable).Value = "Table " & theTable
                toRow(theTable) = 2
            End If
            toSheet.Cells(toRow(theTable), theTable) = fromSheet.Cells(fromRow, 2).Value
            toRow(theTable) = toRow(theTable) + 1
            fromRow = fromRow + 1
    End Sub

    Open in new window

    LVL 50

    Accepted Solution


    a lookup or index will be tricky because the table numbers are not unique values. You CAN use a lookup if you use a helper column. For example in column J enter this formula into cell J2


    Copy down all rows.

    Then you can use a formula on the next sheet, where the table numbers are in row 1


    Or, without a helper column, you could simply use a pivot table to crunch the data. See attached for both scenarios at work.

    cheers, teylyn

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now