I'm trying to check if people were able to indicate "being in two different places at the same time".
https://docs.google.com/spreadsheets/d/1Z9I_5jvgrKKs0ejqJ-PF7FnA6LwHsWUou6xagtaxe3M/edit?usp=sharing
For that :
- I import the presence data of role 1 into a first tab: role1
- I import the presence data of role 2 into a second tab: role2
In this data (which I do not control), attendance is given in days (J01, J02, J03, ...) except that the day numbers do not correspond to the same dates! So I have to find a trick to achieve my goals.
To resolve this first problem, I have access to the schedule so that I can import the schedules and determine the days corresponding to each group (tabs R1, R2, R3, D1, D2, D3): in the tab days, I therefore collect the dates per group and per day. This information allows me to retrieve the "hen - day" pairs by date (tab journee_par_date) thanks to the help found here. So far, no problem.
And that’s where it gets bad! I tried something probably complicated which means that I am at an impasse. To find duplicates of the same person in two different roles, I told myself that it was first necessary to transform the attendances which are noted in number of days (in my example, on the role1 tab, the information noted A1 or A2 (cells C2 to H14)) in corresponding dates. My idea was first to replace A1 and A2 with "hen - day" pairs, which I did in the yellow boxes of the role1 tab. Then then find this same correspondence in the table from the data in the day_by_date tab where I "calculate" the hen-day couple (boxes in orange). But the problem is that I can't figure out how I can search for a value (for example the value of cell L2 of the role1 tab) in a table with several rows and columns (in my example, cells J1 to P14 of the day_by_date tab.
I hope I was clear enough in explaining my problem. If anyone could give me a hand.
Perhaps a solution that has nothing to do with what I imagined would be more relevant in which case, I will be happy to take it!
THANKS