Aug 05 2020 01:40 AM
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.
Aug 05 2020 01:50 AM
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)
Aug 05 2020 01:59 AM
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
Aug 05 2020 02:09 AM
Aug 05 2020 04:12 AM
Hi again,
Would you mind writing the formula directly in the Excel sheet? :)
Aug 05 2020 04:42 AM
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)
Aug 05 2020 07:01 AM
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
Aug 05 2020 08:38 AM
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
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
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.
Aug 05 2020 09:18 AM
Aug 05 2020 10:46 AM
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
Aug 05 2020 10:54 AM
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
Aug 06 2020 07:13 AM
Maria,
Second question - I see, thank you. For it you need list of rooms
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))#
Aug 06 2020 08:18 AM
Selected employees per room - so far have no proper solution, will try to find some later.
Aug 06 2020 10:45 AM
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. :)
Aug 06 2020 12:42 PM
Hi Maria - sorry for misunderstanding, that's clear now. Will be back.
Aug 06 2020 12:59 PM
Second question:
Visualization like
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
Aug 06 2020 02:49 PM
SolutionThird question. Solution is bit straightforward, but perhaps works correctly.
So, in Room Allowance sheet we keep matrix like this
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.
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
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.
Aug 17 2020 07:16 AM
Thank you SO much for your help!!! Very much appreciated.
Have a nice day, and sorry for my late response.
Best regards,
Maria
Aug 17 2020 09:03 AM - edited Aug 17 2020 09:04 AM
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
Aug 19 2020 03:33 PM
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....
Aug 06 2020 02:49 PM
SolutionThird question. Solution is bit straightforward, but perhaps works correctly.
So, in Room Allowance sheet we keep matrix like this
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.
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
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.