Formula for a seating plan

[Liverlee]

Hi all,

I have another task that i hope you can help me with

I have a spreadsheet that looks like this.


COLUMN A, COLUMN B, COLUMN C, COLUMN D, COLUMN E
Firstname , Surname , Location , Grade , Table no.
Bill , Smith , London , 1 , 1
Jane , Doe , Paris , 1 , 2
Simon , Says , New York , 2 , 1
John , Lock , Rome , 3 , 2

I have sample 200 names, 40 locations, 7 grades, and i need a formula that will generate a table number in column E.

The idea is where possible that people of different grades & locations sit together.

We are trying to avoid 5 people from rome sitting at the same table, or 5 people at grade 1 sitting at the same table.

There are 40 tables that will seat 5 people.

So in Column E i need it to say 1 upto 40.

Any suggestions?

 

[jkicker]

You won't be able to do this with an in-cell formula. you'll need some VBA code to run some loops on some variables, and set up some arrays with parameters about the tables. You have quite a bit of work ahead of you. i'm new to this so it would take me far too long to write something for you. sorry i'm not more helpful. i hope that i gave you some things to start reading about so that you could move forward.

 

[Liverlee]

Thank J, i appreciate you taking the time to respond and pointing me towards where i need to go and research.

 

[Joe4]

This could end up being a complex solution, one that may be difficult to get solved from a free forum.
If you cannot figure it out, you might want to enlist the help of a consultant. There are a few listed here: https://www.mrexcel.com/consulting-services/