[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1180
  • Last Modified:

need help with excel function! trying to create seating chart

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?
0
jsctechy
Asked:
jsctechy
1 Solution
 
tdlewisCommented:
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
    Loop
    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
    Loop
End Sub

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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

=COUNTIF($I$1:$I2,I2)

Copy down all rows.

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

=INDEX(Sheet1!$B$1:$B$100,MATCH(Sheet2!A$1&ROW(A1),INDEX(Sheet1!$I$1:$I$100&Sheet1!$J$1:$J$100,0),0))

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
Book2.xlsx
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now