SOLVED

Formula help :)

Copper Contributor

Hi everybody,

I am new here, and I really hope that you will be able to help me on this (Please find the excel file attached):

 

How do I make a formular that ensures that no employees are listed twice (or more) in each row?

And how do I - still "automatically" make en additional scheme showing the employees' names in the first row instead of the rooms? 

 

Thank you very much in advance. :)

 

Best wishes,

Maria S.

29 Replies

@mariasolhoej 

 

Here is a small example with conditional formatting.
It is easiest for the user to see everything at a glance.

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

Hi @NikolinoDE 

Thanks for your reply. :)

It was not excactly what I was looking for. I am more interested in a formular making the listing with no errors :)

Best wishes,

Maria

in formula for each row
=IF(MATCH(A13,$B2:$N2,0)=ROW(),"","double")

Hi again,

Would you mind writing the formula directly in the Excel sheet? :)

 

@mariasolhoej 

 

Please take a look at this proposal with data verification.
the first row (B2: N2) has been made as an example.

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

Hi again, @NikolinoDE :)

Thank you very much, however, I don't read your suggestion in the attached file? 

Would you mind attaching it again, using another filename?

Thank you very much for your kind willingness.

Best regards,

Maria

@mariasolhoej 

If you are on Excel with dynamic arrays that could be done with helper ranges which could be created at any place of your workbook (better in separate sheet)

Here

image.png

starting from B12 make headers for the time as

=TRANSPOSE($A$2:$A$8)

In B13

=SORT(FILTER($A$13:$A$32,COUNTIF(INDEX($B$2:$N$8,MATCH(B$12,$A$2:$A$8,0),0),$A$13:$A$32)=0))

(it shows names are not assigned for that time) and drag it to the right.

 

Select your entire range and add data validation to all cells as

image.png

Formula is

=INDEX($B$13:$H$13,0,ROW()-ROW($A$1))#

 

As for the second question I didn't catch, could you please illustrate in file.

@mariasolhoej 

 

Please see the information in the worksheet.

Dear @Sergei Baklan ,

Thank you - very helpful!! :)

I have tried to illustrate the matter related to my second question - please see attached file. 

Best regards,

Maria

@Sergei Baklan 

Another questions if okay:

 

Could you also set up a rule using a formular so that only certain employees (from the list) can be selected for special rooms:

 

Ex:
ROOM 1: Only AA; AB; AC; AD; AE and AJ can stay here

ROOM 2: Only AA, AC, AM, AS and AR can stay here

and so on.... ?

Best regards,

Maria

 

@mariasolhoej 

Maria,

Second question - I see, thank you. For it you need list of rooms

image.png

Formulas like

=FILTER($A$50:$A$62,COUNTIF(INDEX($B$12:$U$18,MATCH(B$49,$A$12:$A$18,0),0),$A$50:$A$62)=0)

Data validation

=INDEX($B$50:$H$50,0,ROW()-ROW($A$11))#

@mariasolhoej 

Selected employees per room - so far have no proper solution, will try to find some later.

Hi again @Sergei Baklan 

I think I didn't succeed in explaining it the right way. :)

So what I actually wanted (regarding my second question) is only a change in the way the data will be visualized in the Excel file.

 

So, actually I only want to be able to pick certain employees and put them into a certain room - not the opposite.

However, in addition to the first scheme, I would also like a scheme showing which rooms each employee should stay in. So it's probably some kind of transpose formula I need here? Unforntunately, I don't know how to do it.

Do you understand what I want? :)

Best wishes,

Maria

 

PS: Hope you will find a solution to my third question aswell - that would be awesome. :)

 

@mariasolhoej 

Hi Maria - sorry for misunderstanding, that's clear now. Will be back.

@mariasolhoej 

Second question:

Visualization like

image.png

in B12 is the formula

=XLOOKUP(B$11,$B2:$N2,$B$1:$N$1,"")

and drag it to the right and down on entire range.

Time is better return as reference

=$A$2:$A$8

and headers with names as

=TRANSPOSE($A$24:$A$43)

That's not a good idea to duplicate same data manually in different places.

 

Third question:

Still not sure how to do, just top clarify in which form you keep the list of persons allowed for each room. That could be a matrix like this

image.png

best response confirmed by mariasolhoej (Copper Contributor)
Solution

@mariasolhoej 

Third question. Solution is bit straightforward, but perhaps works correctly.

So, in Room Allowance sheet we keep matrix like this

image.png

That's the only source for employee and room names, in all other places we reference on it.

 

Next is Helper sheet (could be hided) where we keep combinations of all available periods and rooms and build for each such combination list of employees allowed to book the room.

image.png

 In C4

=INDEX(Booking!$C$3:$O$3,1,INT((COLUMN()-COLUMN($B$5)-1)/COUNTA(Booking!$B$4:$B$10))+1)

drag it to the right till error appears.

In C5

=INDEX(Booking!$B$4:$B$10,MOD((COLUMN()-COLUMN($B$5)-1),COUNTA(Booking!$B$4:$B$10))+1,1)

and in C6

=SORT(FILTER(
              'Rooms allowance'!$B$3:$B$22,
              (COUNTIF(INDEX(Booking!$C$4:$O$10,MATCH(C$5,Booking!$B$4:$B$10,0),0),'Rooms allowance'!$B$3:$B$22)=0)*
              (INDEX('Rooms allowance'!$C$3:$O$22,0,XMATCH(C$4,'Rooms allowance'!$C$2:$O$2))="x")
))

Drag them also to the right (or select the range and CTRL+R).

 

Data validation for entire booking range

image.png

is with formula

=INDEX(Helper!$C$6:$CO$6,1,XMATCH(C$3&$B4,Helper!$C$4:$CO$4&Helper!$C$5:$CO$5))#

 

Solution is very depends on number of employees, I assumed relatively small one.

Another variant which I tried, without helper, doesn't work since data validation has 256 characters limit on formula length and now I don't see the way how to fit it.

Thank you SO much for your help!!! Very much appreciated.

Have a nice day, and sorry for my late response.

 

Best regards,

Maria

Hi again @Sergei Baklan 

 

So, I worked a bit further with your nice work. :)

 

Now, I would really like if I could use your "Helper" pr. day, however be able to see one week pr. sheet (One week = Monday, Tuesday, Wednesday, Thursday, and Friday) 

 

Ex: Employer "CATT" should be allowed to stay with "7v" Monday morning from 8-8.45 and 8.45-9.30 and with 7x Tuesday morning 8-8.45 and 8.45-9.30, and so on.... I have tried to fix the formula but it doesn't seem to accept my changes. :)

Do you understand what I want?

 

Best regards,

Maria

 

@mariasolhoej 

Hi Maria,

That's bit different task. As a starting point allowance shall be defined somehow, not sure how to do that better. Perhaps deformalized table as

CATT Mon 8-8.45 7v

CATT Mon 8.45-9.30 7v

CATT Mon 9.30-10.15 6c

CATT Tue 8.45-9.30 7v

etc, but it will be quite long list which hard to maintain.

 

Another variant create table for each person with time vs room. Or like, have no proper solution right now.

 

 

 

" should be allowed to stay with "7v" Monday morning from 8-8.45 and 8.45-9.30 and with 7x Tuesday morning 8-8.45 and 8.45-9.30, and so on....

1 best response

Accepted Solutions
best response confirmed by mariasolhoej (Copper Contributor)
Solution

@mariasolhoej 

Third question. Solution is bit straightforward, but perhaps works correctly.

So, in Room Allowance sheet we keep matrix like this

image.png

That's the only source for employee and room names, in all other places we reference on it.

 

Next is Helper sheet (could be hided) where we keep combinations of all available periods and rooms and build for each such combination list of employees allowed to book the room.

image.png

 In C4

=INDEX(Booking!$C$3:$O$3,1,INT((COLUMN()-COLUMN($B$5)-1)/COUNTA(Booking!$B$4:$B$10))+1)

drag it to the right till error appears.

In C5

=INDEX(Booking!$B$4:$B$10,MOD((COLUMN()-COLUMN($B$5)-1),COUNTA(Booking!$B$4:$B$10))+1,1)

and in C6

=SORT(FILTER(
              'Rooms allowance'!$B$3:$B$22,
              (COUNTIF(INDEX(Booking!$C$4:$O$10,MATCH(C$5,Booking!$B$4:$B$10,0),0),'Rooms allowance'!$B$3:$B$22)=0)*
              (INDEX('Rooms allowance'!$C$3:$O$22,0,XMATCH(C$4,'Rooms allowance'!$C$2:$O$2))="x")
))

Drag them also to the right (or select the range and CTRL+R).

 

Data validation for entire booking range

image.png

is with formula

=INDEX(Helper!$C$6:$CO$6,1,XMATCH(C$3&$B4,Helper!$C$4:$CO$4&Helper!$C$5:$CO$5))#

 

Solution is very depends on number of employees, I assumed relatively small one.

Another variant which I tried, without helper, doesn't work since data validation has 256 characters limit on formula length and now I don't see the way how to fit it.

View solution in original post