Hi
How to explain this. I have list of people who have all developed Covid symptoms. I have their gate times so all their movement around site is tracked and I'm trying to work out if they've come into contact with each other and that's how they caught the virus.
I know I can use a macro to do this but want to check if it can be done with formulas first.
In column A I have date and time in 10 minute intervals for several weeks. Each person is in column B, C, and D etc with which gate they went through. I'm trying to write a formula so it can look down a list of gate times for someone and plot it in the correct place in my 10 minute intervals. I can do this up to a point see below. The formula I have is-
=IF(AND(E$1>A4,E$1<A5),$F$1,IF(AND(E$2>A4,E$2<A5),$F$2,IF(AND(E$3>A4,E$3<A5),$F$3,IF(AND(E$4>A4,E$4<A5),$F$4,IF(AND(E$5>A4,E$5<A5),$F$5,IF(AND(E$6>A4,E$6<A5),$F$6,""))))))
E1-E6 = Date and Time of person
F1-F6 = which gate they went through
Info. at the bottom is column A and B. This formula goes in column B.
The issue is this only covers 6 lines of gate entries for someone. Some people can have a few hundred gate times so this is not practical! And I have 200 people to look at which I need to put side by side.
Can anyone think of an easier formula to write rather than repeating the one above hundreds of times? I've attache a file for clarity.
Thanks
Mike
How to explain this. I have list of people who have all developed Covid symptoms. I have their gate times so all their movement around site is tracked and I'm trying to work out if they've come into contact with each other and that's how they caught the virus.
I know I can use a macro to do this but want to check if it can be done with formulas first.
In column A I have date and time in 10 minute intervals for several weeks. Each person is in column B, C, and D etc with which gate they went through. I'm trying to write a formula so it can look down a list of gate times for someone and plot it in the correct place in my 10 minute intervals. I can do this up to a point see below. The formula I have is-
=IF(AND(E$1>A4,E$1<A5),$F$1,IF(AND(E$2>A4,E$2<A5),$F$2,IF(AND(E$3>A4,E$3<A5),$F$3,IF(AND(E$4>A4,E$4<A5),$F$4,IF(AND(E$5>A4,E$5<A5),$F$5,IF(AND(E$6>A4,E$6<A5),$F$6,""))))))
E1-E6 = Date and Time of person
F1-F6 = which gate they went through
Info. at the bottom is column A and B. This formula goes in column B.
The issue is this only covers 6 lines of gate entries for someone. Some people can have a few hundred gate times so this is not practical! And I have 200 people to look at which I need to put side by side.
Can anyone think of an easier formula to write rather than repeating the one above hundreds of times? I've attache a file for clarity.
Thanks
Mike
Person A | |
17/06/2020 08:00 | |
17/06/2020 08:10 | |
17/06/2020 08:20 | Main Gate1 |
17/06/2020 08:30 | |
17/06/2020 08:40 |