Complex formula to create seat assignment

Hello everyone,

i am looking to create some formulas that will allow me to do the following:


See if a number is present in an array
if the number is present, where it is located according to a specific table.

the goal is to be able to assign seats for an event that i am doing and make sure that all are seated

below is a picture of what my excel looks like

Floor plan here


i need to populate the following column



Seated
Row
Seat Number


Any help is welcome

Thank you

hello and welcome to forum

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

in E2 copy paste below then hold control and shift together and then hit enter to make it array formula and drag down

Formula:

Please Login or Register  to view this content.

In F2 copy paste below then hold control and shift together and then hit enter to make it array formula and drag down

Formula:

Please Login or Register  to view this content.

Array...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

For the row, paste this into E2 and copy down:

=IF(COUNTIF($L$4:$AT$4,A2),$K$4,IF(COUNTIF($L$5:$AT$5,A2),$K$5,IF(COUNTIF($L$12:$AT$12,A2),$K$12,IF(COUNTIF($L$13:$AT$13,A2),$K$13,""))))

you are welcome Dideierkassas!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You can thank here by clicking add reputation icon below the post of the members who helped you, this adds to the reputation of member shown in form of green bar below their names

Originally Posted by didierkassas

That's perfect it worked, it is quite a complex formula. thank you

It would be helpful if Hemesh could explain how it works so that we can learn from this.

@ AliGW ! which part would you like to know.

Originally Posted by hemesh

@ AliGW ! which part would you like to know.

Can you explain the use of MMULT and TRANSPOSE? I understand INDEX and MATCH.

Originally Posted by didierkassas

for the Seat, i just copy the same formula?

Who are you asking? If you are asking me, then no: use Hemesh's solution. There are two formulae, one for the row and one for the seat.

it's ok i found the error, the selection had shifted

now all is working perfectly

@ AliGW, MMULT Stands for matrix multiplication and needs two arrays
first array checks if value is available and generates array of 0's and 1's
--(($L$4:$AT$5=$A2))

Second array
TRANSPOSE(COLUMN($L$4:$AT$4)^0)
converts columns into rows and push the value of 1 as any number raised to power 0 is 1 and gives the results as {0,1} or {0,0} or {1,0}

Hope that helps

@Hemesh

I think there is enough here for me to work the rest out. Thank you!

You are welcome AliGW.

Originally Posted by hemesh

@ AliGW, MMULT Stands for matrix multiplication and needs two arrays
first array checks if value is available and generates array of 0's and 1's
--(($L$4:$AT$5=$A2))

Second array
TRANSPOSE(COLUMN($L$4:$AT$4)^0)
converts columns into rows and push the value of 1 as any number raised to power 0 is 1 and gives the results as {0,1} or {0,0} or {1,0}

Hope that helps

I have just managed to get round to looking at this in detail. I understand the MMULT section, but the thing that is confusing me is how the TRANSPOSE section could ever result in 0. Can anyone advise? I must be missing something obvious ...

Originally Posted by AliGW

how the TRANSPOSE section could ever result in 0. Can anyone advise?

It can't!

TRANSPOSE is used to generate a VERTICAL array of 1s.

{1;1;1;1;1} = vertical array
{1,1,1,1,1} = horizontal array

Any number to the power of 0 = 1.

That's what I thought, Tony, so how does this work?

TRANSPOSE(COLUMN($L$4:$AT$4)^0)
converts columns into rows and push the value of 1 as any number raised to power 0 is 1 and gives the results as {0,1} or {0,0} or {1,0}

Let's assume the range is A1:E1...

TRANSPOSE(COLUMN(A1:E1)^0)

COLUMN(A1:E1)^0 generates this horizontal array:

{1,1,1,1,1}

Then TRANSPOSE turns it into a vertical array:

TRANSPOSE({1,1,1,1,1})

{1;1;1;1;1)

MMULT is a very difficult function to understand. Years ago I wrote this explanation for someone:

http://www.tech-archive.net/Archive/.../msg00758.html

Thanks, Tony. I do understand the MMULT section and now the TRANSPOSE section, but what I don't get is how they result in the {0,1}, {0,0} or {1,0} mentioned. This is where I am at a loss.

After reading it many times...

Originally Posted by hemesh

...and gives the results as {0,1} or {0,0} or {1,0}

hemesh might have meant that those are the results when the 2 arrays are multiplied together...

--(($L$4:$AT$5=$A2)) * TRANSPOSE(COLUMN($L$4:$AT$4)^0)

Thanks, Tony. I'm afraid I'm just not getting this at all, but thanks for trying to enlighten me!

As Tony sir said, in the post #25, it may be any result out of {0,0} or {0,1} or {1,0}.

Please go through Post #23, Link provided by Tony sir, you will be understanding a lot about it.

Originally Posted by hemesh

As Tony sir said, in the post #25, it may be any result out of {0,0} or {0,1} or {1,0}.

Please go through Post #23, Link provided by Tony sir, you will be understanding a lot about it.

You might be able to tell from my avatar that I am female.

I have looked at Tony's link and it has not helped me. It would be really helpful (but if you don't have time to, I'll understand) if you could go through step-by-step to explain what is happening. I have tried using the Evaluate Formula option, but I haven't yet had the light bulb moment.

{0,0} or {0,1} or {1,0}

How exactly do we arrive at these three possible results?

@ Ali,
first part --(($L$4:$AT$5=$A2)) check value of A2 in a matrix of 2 rows and 35 columns and generates array of 0's and 1's as below in same matrix of 2R and 35C
{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
matched value have results of 1's and unmatched have value of 0

now second array TRANSPOSE(COLUMN($L$4:$AT$4)^0), transpose converts column into row and and ^0 converts columns numbers as 1's as below
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}

now both matrices generated from array one and array two gets multiplied and throws the results in form of 2 rows and 1 column
expected results that we can interpret are
if there is no match found in array one then it will have all zeros thus result will be {0;0}
if there is match is found in array one in first row then result will be {1;0}
if there is match found in array one in second row then result will be {0;1}

Thanks, Hemesh - I'll study this.

@ Ali, You are welcome.