SandsB
Well-known Member
- Joined
- Feb 13, 2007
- Messages
- 705
- Office Version
- 365
- Platform
- Windows
I need to know when clients call in more than once within 3 days of their first call for an event. Clients can call in many time over the course of a month so I need to look at all calls within 3 days of each other as calls for the same event. Calls from the same client with a gap of more than 3 days will be considered different events.
Thousands of rows of data sorted in Date/time order. The Date/Time is in column A. The client's name is in column B. If the client has multiple records, I need to add an 'X' in column C on the row where he called in the first time (and only on that row) and there's at least one other record within 3 days of that first record. That same client might call in several times and I don't want the X on other rows unless there's been a gap of at least 3 days, then I'd consider the start of a new sequence of the client calling in.
EX:
Thousands of rows of data sorted in Date/time order. The Date/Time is in column A. The client's name is in column B. If the client has multiple records, I need to add an 'X' in column C on the row where he called in the first time (and only on that row) and there's at least one other record within 3 days of that first record. That same client might call in several times and I don't want the X on other rows unless there's been a gap of at least 3 days, then I'd consider the start of a new sequence of the client calling in.
EX:
Row # | Date/Time | Client | Repeat | |
2 | 1/2/19 4:15 PM | 1111 | X | 1/2/2019 4:23:58 PM is less than 3 days since the record in row 3 |
3 | 1/2/19 4:23 PM | 1111 | There's a call within 3 days of this one but row 4 is within 3 days of row 3 so - same event = don't flag it. | |
4 | 1/3/19 1:23 PM | 1111 | ||
5 | 1/4/19 6:19 PM | 2222 | ||
6 | 1/4/19 7:21 PM | 1111 | This is a repeat but it's been less than 3 days since client 1111 in row 3 | |
7 | 1/8/19 8:09 PM | 1111 | Another repeat, more than 3 days since row 6 so I'd consider it a new event but there's no record within 3 days after this one. | |
8 | 1/12/19 10:07 PM | 1111 | X | Same client and there's a record within 3 days in row 9 |
9 | 1/13/19 10:46 PM | 1111 | ||
10 | 1/16/19 11:11 PM | 2222 | X | 1/16/2019 11:31:58 PM in row 11 is less than 3 days since the event in row 10 |
11 | 1/17/19 11:31 PM | 2222 | ||
12 | 1/19/19 2:41 AM | 3333 | X | 1/21/2019 5:04:51 PM in row 14 is less than 3 days since the event in row 12 |
13 | 1/20/19 2:20 PM | 4444 | ||
14 | 1/21/19 1:04 PM | 3333 |