Flag for Multiple Values within 3 mins of each other

[MagiCarty]

Trying to flag any teachers that have more than 1 submission wihtin 3 mins of each other. I'm terrible with arrays but I'm guessing an index/match might work here? Example data below:


Submission Form Version Date Time SELECT TEACHER
1 4/22/2014 3:36 PM Teacher 1
1 4/22/2014 3:43 PM Teacher 1
1 4/22/2014 3:50 PM Teacher 1
1 4/22/2014 4:31 PM Teacher 1
1 4/23/2014 4:39 PM Teacher 2
1 4/23/2014 4:49 PM Teacher 2
1 4/23/2014 5:19 PM Teacher 2
1 4/22/2014 1:46 PM Teacher 3
1 4/22/2014 1:53 PM Teacher 3
1 4/22/2014 1:57 PM Teacher 3
1 4/22/2014 2:01 PM Teacher 3
1 4/23/2014 12:04 PM Teacher 3
1 4/22/2014 11:44 AM Teacher 4
1 4/22/2014 1:55 PM Teacher 4
1 4/22/2014 1:31 PM Teacher 5
1 4/22/2014 1:40 PM Teacher 5
1 4/22/2014 5:43 PM Teacher 5
1 4/23/2014 1:17 PM Teacher 5
1 4/23/2014 4:07 PM Teacher 5
1 4/24/2014 2:01 PM Teacher 5
1 4/24/2014 2:19 PM Teacher 5
1 4/24/2014 2:28 PM Teacher 5
1 4/23/2014 12:41 PM Teacher 6
1 4/23/2014 12:54 PM Teacher 6
1 4/23/2014 2:12 PM Teacher 6
1 4/23/2014 2:35 PM Teacher 6
1 4/22/2014 12:54 AM Teacher 7
1 4/22/2014 5:39 PM Teacher 7
1 4/22/2014 6:10 PM Teacher 7

[Jacc]

Assuming time is in column C and teachers are in column E, then this might work:

Please Login or Register  to view this content.

Past in F2 and copy down

[MagiCarty]

Thank you. I tried adding that, but changed it to: =IF(AND((C3-C2)<"00:03"*1,E3=E2),"Flag","") because the 03:00 appears to be checking within 3 hours, and not minutes like I'm looking for.

This doesn't appear to be working as it is flagging some with drastically different times on them. I despise times/dates in Excel. I also need to make sure the times are within the same dates or potentially when they cross over from midnight. Criteria is same Teacher Name within 3 minutes of a previous entry. So probably need to incorporate date in there too.

[playaller]

Updating Formula

[playaller]

Assuming your time data starts in C2. Teachers start in D2...

Paste in E3 and Copy down:

Please Login or Register  to view this content.

[MagiCarty]

I think this will work. Thank you very much.

[playaller]

You're more than welcome my friend!

Feel Free to Click the star under my name to Add Reputation if my resolution worked. Also don't forget to mark the thread as RESOLVED by clicking on Thread Tools on the top of this board and clicking RESOLVED! Thank you!