seating chart

[Guest]

page 1 of my worksheet has a list of names, meals and table numbers that I type
page 2 has a list of table numbers (the seating chart)
how do i populate the page 2 table numbers automatically without using pivot
tables?

 

[Max]

(Re-sent, apologies for the unintentioned advance dating)

Maybe something along these lines ..

Assume you have the sample table below
in Sheet1, in A1:B10

Names Table#
Name1 2
Name2 3
Name3 4
Name4 1
Name5 3
Name6 3
Name7 4
Name8 1
Name9 2

List across in E1:H1, the 4 table numbers: 1,2,3,4

Put in E2: =IF($B2=E$1,ROW(),"")
Copy E2 across to H2, fill down to H10

In Sheet2
---------
List across in A11, the 4 table numbers: 1,2,3,4

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!E$2:E$10,ROWS($A$1:A1)),Sheet1!E$2:E$10,0)),"
",INDEX(Sheet1!$A$2:$A$10,MATCH(SMALL(Sheet1!E$2:E$10,ROWS($A$1:A1)),Sheet1!
E$2:E$10,0)))

Copy A2 across to D2, fill down as many rows
as was done in cols E to H in Sheet1, i.e. down to D10

The above will extract and list the names from Sheet1
under each table number

Adapt / extend to suit

 

[Guest]

thanks. that works for the names, however i also need to extrat the 'meal'
next to the person. i tried the lookup but since the data is not sorted it
doesn't work. any suggestions?

 

[Max]

Taking a simple extension
to the assumed set-up in Sheet1
where "Meal" is placed in col C, i.e.:

Names Table# Meal
Name1 2 Meal1
Name2 3 Meal2
Name3 4 Meal3
etc

Make a concatenated field in col D
to join the Name to the Meal

Put in D2: =A2&"-"&C2
Copy down to D10

In Sheet2
-------------
Just replace the formula in A2 by:

=IF(ISERROR(MATCH(SMALL(Sheet1!E$2:E$10,ROWS($A$1:A1)),Sheet1!E$2:E$10,0)),"
",INDEX(Sheet1!$D$2:$D$10,MATCH(SMALL(Sheet1!E$2:E$10,ROWS($A$1:A1)),Sheet1!
E$2:E$10,0)))

Copy across and fill down as before

The amendment made was to the ...INDEX(...) part,
to point now to col D instead of col A (in Sheet1), viz.:

.... INDEX(Sheet1!$A$2:$A$10

changed to:

.....INDEX(Sheet1!$D$2:$D$10

The above will return:the concat "Name-Meal"s
under each table number

 

[Don Guillett]

Max, Your dates are WAY OFF. If by mistake, please correct. I automatically
delete these but have ONCE been guilty.

 

[Max]

Max, Your dates are WAY OFF. If by mistake, please correct. I automatically
delete these but have ONCE been guilty.

Yes, my apologies .. by 24 years, I'm afraid. Corrected immediately the
same day - 3 Jan, upon alert by Dave Peterson via a response in
..public.excel (Chuck also sent a private alert a couple of days later)
FWIW, lame excuse: Desktop's harddisk reformatted recently ..

 

[Max]

Oops, typo detected:

.. by 24 years

should read :