Code to identify the person seating next to the Left, Center, or on the Right of an input/entered persons name.

[KidlatKulug1]

Hi VBA expert coder,
I'm not sure how to create an excel file so it'll be easier for someone creating the macro code to run better. Here are my known data:
-there are 72 tables that can seat 3 individuals (Left, Center, Right), seat # 1 is Left, seat # 2 is Center, seat # 3 Right, seat # 4 is Left, seat # 5 is Center, seat # 6 is Right, seat # 7 is Left, seat # 8 is Center, seat # 9 is Right, and so on...
-Group of 7 (maximum of 84 people in each group) with exception of group 4 with 108 people
-Each group are subdivided into smaller groups of 3 (Left, Center, Right), each individual already been given designated seat #
~Group 1 will be at seat # 1-84, meeting start at 10am
~Group 2 seating # from 1-82, meeting start at 10:30am
~Group 3 seating # from 1-84, meeting start at 11:00am
~Group 4 seating # from 1-108, meeting start at 11:30am
~Group 5 seating # from 109-207, meeting start at 10:15am
~Group 6 seating # from 109-197, meeting start at 10:45am
~Group 7 seating # from 109-207, meeting start at 11:15am
Now here is the tricky part. I need an input box requesting to type/enter the Name of a person to search. The display output should be the seat # and also if its at the Left, Center, or Right of the table. And if that person is seating at the center of a table I also need the individual Names on the Left and on the Right seated next to him/her (example: the output searched was seat # 5 and its at the center of the table, the Names seated at # 4 and 6 should be displayed as well). But if the input/search person is seated on the Right of the table (for example seat # 9), the output display should be the name of the person sitting at the center of the table closest to him/her which means seat # 8. And if the searched person is seated on the Left of the table (seat # 4), the output should display the name of the person sitting at the center closest to him/her which means seat # 5.
Can someone please help me generate an excel file, so I can start entering the Names in a correct format. I was thinking of entering the seat # with designated Left, Center, Right next to each # in column A and Group 1-7 in column B-H, correct me if this is not the best way.

Any help is much appreciated

 

[Jeffrey Mahoney]

You don't need a macro. A table of names with a seat number will do.

Book1
	A	B	C	D	E
1	Name	Seat #	Position	Person to the Left	Person to the Right
2	Joe Blow	1	Left		Fred Dead
3	Jack Black	72	Right	Unassigned
4	Jane Spain	10	Center	Unassigned	Unassigned
5	Buffy Spiffy	18	Right	Unassigned
6	Georgie Porgie	19	Left		Frank Spank
7	Frank Spank	20	Center	Georgie Porgie	Heather Heaven
8	Heather Heaven	21	Right	Frank Spank
9	Ted Head	73	Left		Unassigned
10	Lou Blue	75	Right	Unassigned
11	Fred Dead	2	Center	Joe Blow	Opal Apple
12	Opal Apple	3	Right	Fred Dead
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF(MOD(B2,3)=0,"Right",IF(MOD(B2,2)=0,"Center","Left"))
D2:D12	D2	=IF(C2<>"Left",IFERROR(XLOOKUP(B2-1,B:B,A:A),"Unassigned"),"")
E2:E12	E2	=IF(C2<>"Right",IFERROR(XLOOKUP(B2+1,B:B,A:A),"Unassigned"),"")

 

[KidlatKulug1]

You don't need a macro. A table of names with a seat number will do.

Book1
	A	B	C	D	E
1	Name	Seat #	Position	Person to the Left	Person to the Right
2	Joe Blow	1	Left		Fred Dead
3	Jack Black	72	Right	Unassigned
4	Jane Spain	10	Center	Unassigned	Unassigned
5	Buffy Spiffy	18	Right	Unassigned
6	Georgie Porgie	19	Left		Frank Spank
7	Frank Spank	20	Center	Georgie Porgie	Heather Heaven
8	Heather Heaven	21	Right	Frank Spank
9	Ted Head	73	Left		Unassigned
10	Lou Blue	75	Right	Unassigned
11	Fred Dead	2	Center	Joe Blow	Opal Apple
12	Opal Apple	3	Right	Fred Dead
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF(MOD(B2,3)=0,"Right",IF(MOD(B2,2)=0,"Center","Left"))
D2:D12	D2	=IF(C2<>"Left",IFERROR(XLOOKUP(B2-1,B:B,A:A),"Unassigned"),"")
E2:E12	E2	=IF(C2<>"Right",IFERROR(XLOOKUP(B2+1,B:B,A:A),"Unassigned"),"")

You don't need a macro. A table of names with a seat number will do.

Book1
	A	B	C	D	E
1	Name	Seat #	Position	Person to the Left	Person to the Right
2	Joe Blow	1	Left		Fred Dead
3	Jack Black	72	Right	Unassigned
4	Jane Spain	10	Center	Unassigned	Unassigned
5	Buffy Spiffy	18	Right	Unassigned
6	Georgie Porgie	19	Left		Frank Spank
7	Frank Spank	20	Center	Georgie Porgie	Heather Heaven
8	Heather Heaven	21	Right	Frank Spank
9	Ted Head	73	Left		Unassigned
10	Lou Blue	75	Right	Unassigned
11	Fred Dead	2	Center	Joe Blow	Opal Apple
12	Opal Apple	3	Right	Fred Dead
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF(MOD(B2,3)=0,"Right",IF(MOD(B2,2)=0,"Center","Left"))
D2:D12	D2	=IF(C2<>"Left",IFERROR(XLOOKUP(B2-1,B:B,A:A),"Unassigned"),"")
E2:E12	E2	=IF(C2<>"Right",IFERROR(XLOOKUP(B2+1,B:B,A:A),"Unassigned"),"")

I could probably work/use this but my desired result is to have a macro and have the result display on a different worksheet for only the searched name. I like to keep the list of name in private/protected worksheet

 

[KidlatKulug1]

anybody else have any idea how to tackle this project in VBA code?

 

[Jeffrey Mahoney]

I can write very complex VBA code, but this seems like you need to create another table that references the main data.

 

[KidlatKulug1]

I can write very complex VBA code, but this seems like you need to create another table that references the main data.

Yes, i prefer to have a Master table where it'll ask the Names to be search (Maximum of 4) and have desired output/display after input box is executed. The list of names to search will be in separate worksheet.
Note:
-The search to be Not case sensitive.
-Only display the searched Name and the adjacent Name(s) and their sit assignment.
-Left, Center, Right are assigned in numerical order (ie, seat #1 is Left, seat #2 is Center, seat#3 is Right, seat #4 is Left, seat #5 is Center, seat #5 is Right. and so on repeatedly.
-Being said that, if the Name searched is seated in # 24 (which is Right), I only need output display to be the Name seated on # 23 (which is Center). Same with searched Name seated in #76, I only need the output display the Name seated on # 77 (which is Center). But when the Name searched resulted someone seat in # 29, I need both Names (Left and Right of that Center).

Thank you very much for taking the time to help me.