Counting Consecutive Days based on Criteria

[zcavaricci]

Hello,

I need to count the number occurences where blood sugar was >180 2x in a Day for =>3 consecutive days by patient. I have one table with the following structure:

Date/Time	Patient ID	Glucose Reading
1/28/2017 17:36	A	192
1/28/2017 20:12	A	311
1/29/2017 9:58	A	214
1/29/2017 10:58	A	181
1/30/2017 20:45	A	279
1/30/2017 21:45	A	181
1/31/2017 7:48	A	157
1/31/2017 12:33	A	157
1/31/2017 17:48	A	167
1/28/2017 12:35	B	158
1/29/2017 12:09	B	190
1/29/2017 17:05	B	211
1/30/2017 7:41	B	197
1/30/2017 12:05	B	211
1/30/2017 17:31	B	231
1/31/2017 21:26	B	188
1/5/2017 22:05	C	205
1/5/2017 23:40	C	216
1/6/2017 3:18	C	195
1/6/2017 7:54	C	187
1/7/2017 12:54	C	210
1/7/2017 17:22	C	379
1/9/2017 7:37	C	191

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Results Expected:

Patient	# Occurrences
A	1
C	1

<tbody>
</tbody>

 

[xenou]

What should be done if patient only has one results on a day? Example, patient B on 1/31 in post #1

 

[zcavaricci]

Patient be would not be counted. You have to have 2 results >180 in a day to be counted.

 

[zcavaricci]

If I do 1 patient with the data I supplied here I get the correct answer, when I apply it to my spreadsheet with 25833 rows of data (1281 patients), I don't get the correct answer? Is there a specific sort that I should apply (Sort by Date/Time, Patient or Patient, Date/Time)?

Appreciate all the help here!

Below is the data as supplied to me without any sorting (by Date/Time).

Date and Time	Patient ID	Glu	>180 2x in a day
1/1/2017 0:08	C	98	FALSE
1/1/2017 0:56	A	102	FALSE
1/1/2017 3:56	A	159	FALSE
1/1/2017 6:13	C	108	FALSE
1/1/2017 7:58	A	139	FALSE
1/1/2017 11:29	A	171	FALSE
1/1/2017 11:41	C	424	FALSE
1/1/2017 16:14	A	172	FALSE
1/1/2017 16:41	C	153	FALSE
1/1/2017 20:57	A	152	FALSE
1/1/2017 21:23	C	106	FALSE
1/2/2017 0:10	A	122	FALSE
1/2/2017 4:25	A	160	FALSE
1/2/2017 7:27	C	39	TRUE
1/2/2017 7:39	A	140	FALSE
1/2/2017 7:48	C	50	TRUE
1/2/2017 8:19	C	123	TRUE
1/2/2017 10:25	C	282	TRUE	C1
1/2/2017 11:30	A	156	FALSE
1/2/2017 11:40	C	308	TRUE	C1
1/2/2017 15:50	A	142	FALSE
1/2/2017 16:51	C	421	TRUE	C1
1/2/2017 20:16	C	237	TRUE	C1
1/2/2017 20:35	A	150	FALSE
1/2/2017 21:36	C	187	TRUE	C1
1/3/2017 0:31	A	124	FALSE
1/3/2017 4:11	A	162	FALSE
1/3/2017 7:49	C	294	TRUE	C1
1/3/2017 8:27	A	146	FALSE
1/3/2017 11:45	A	145	FALSE
1/3/2017 11:52	C	253	TRUE	C1
1/3/2017 15:37	A	140	FALSE
1/3/2017 17:16	C	228	TRUE	C1
1/3/2017 20:49	A	143	FALSE
1/3/2017 20:55	C	131	TRUE
1/4/2017 0:27	A	136	FALSE
1/4/2017 4:16	A	177	FALSE
1/4/2017 7:42	C	394	TRUE	C1
1/4/2017 9:32	A	149	FALSE
1/4/2017 11:59	C	439	TRUE	C1
1/4/2017 12:20	A	177	FALSE
1/4/2017 13:07	C	481	TRUE	C1
1/4/2017 14:50	C	263	TRUE	C1
1/4/2017 16:08	A	138	FALSE
1/4/2017 17:10	C	198	TRUE	C1
1/4/2017 19:57	A	163	FALSE
1/4/2017 21:32	C	172	TRUE
1/4/2017 23:47	A	128	FALSE
1/5/2017 3:35	A	189	FALSE
1/5/2017 7:08	C	80	TRUE
1/5/2017 8:28	A	144	FALSE
1/5/2017 9:27	C	359	TRUE	C1
1/5/2017 11:19	A	151	FALSE
1/5/2017 11:38	C	368	TRUE	C1
1/5/2017 16:22	A	141	FALSE
1/5/2017 17:20	C	158	TRUE
1/5/2017 20:03	A	141	FALSE
1/5/2017 21:50	C	48	TRUE
1/5/2017 21:53	C	56	TRUE
1/5/2017 22:19	C	65	TRUE
1/5/2017 22:41	C	91	TRUE
1/5/2017 23:13	A	146	FALSE
1/5/2017 23:30	C	190	TRUE	C1
1/6/2017 4:16	A	163	FALSE
1/6/2017 7:03	C	319	TRUE
1/6/2017 9:23	A	162	FALSE
1/6/2017 11:09	A	156	FALSE
1/6/2017 12:01	C	279	TRUE
1/6/2017 16:21	A	130	FALSE
1/6/2017 17:28	C	70	TRUE
1/6/2017 20:46	A	182	FALSE
1/6/2017 21:31	C	58	TRUE
1/6/2017 21:53	C	82	TRUE
1/7/2017 0:18	A	127	FALSE
1/7/2017 0:21	C	205	TRUE
1/7/2017 4:16	A	181	FALSE
1/7/2017 7:46	A	123	FALSE
1/7/2017 8:01	C	74	TRUE
1/7/2017 11:56	C	346	TRUE
1/7/2017 12:30	A	163	FALSE
1/7/2017 13:31	C	328	TRUE
1/7/2017 16:17	A	152	FALSE
1/7/2017 16:58	C	131	TRUE
1/7/2017 21:17	A	105	FALSE
1/7/2017 21:20	C	198	TRUE
1/8/2017 0:43	A	158	FALSE
1/8/2017 3:55	A	147	FALSE
1/8/2017 7:38	A	143	FALSE
1/8/2017 7:45	C	65	TRUE
1/8/2017 11:23	A	154	FALSE
1/8/2017 11:48	C	228	TRUE
1/8/2017 16:13	A	154	FALSE
1/8/2017 17:14	C	244	TRUE
1/8/2017 19:22	A	134	FALSE
1/8/2017 21:07	C	82	TRUE
1/9/2017 0:31	C	158	FALSE
1/9/2017 0:55	A	159	FALSE
1/9/2017 3:50	A	155	FALSE
1/9/2017 7:37	C	91	FALSE
1/9/2017 8:16	A	197	FALSE
1/9/2017 12:07	C	167	FALSE
1/9/2017 13:06	A	148	FALSE
1/9/2017 16:16	A	143	FALSE
1/9/2017 16:46	C	85	FALSE
1/9/2017 20:18	A	149	FALSE
1/9/2017 20:19	C	99	FALSE
1/10/2017 0:05	A	144	FALSE
1/10/2017 4:19	A	144	FALSE
1/10/2017 7:30	C	93	FALSE
1/10/2017 8:26	A	169	FALSE
1/10/2017 11:28	A	172	FALSE
1/10/2017 11:57	C	89	FALSE
1/10/2017 15:27	A	135	FALSE
1/10/2017 16:47	C	50	FALSE
1/10/2017 17:35	C	109	FALSE
1/10/2017 19:33	A	156	FALSE
1/10/2017 20:56	C	107	FALSE
1/10/2017 23:57	A	153	FALSE
1/11/2017 3:55	A	173	FALSE
1/11/2017 7:45	C	116	FALSE
1/11/2017 7:59	A	176	FALSE
1/11/2017 11:27	A	161	FALSE
1/11/2017 11:56	C	131	FALSE
1/11/2017 16:06	A	143	FALSE
1/11/2017 16:54	C	62	FALSE
1/11/2017 18:12	C	302	FALSE
1/11/2017 19:48	A	136	FALSE
1/11/2017 21:09	C	171	FALSE
1/11/2017 23:57	A	161	FALSE
1/12/2017 3:32	A	133	TRUE
1/12/2017 7:44	A	191	TRUE
1/12/2017 7:50	C	47	TRUE
1/12/2017 7:51	C	41	TRUE
1/12/2017 8:12	C	69	TRUE
1/12/2017 8:49	C	253	TRUE
1/12/2017 11:15	A	153	TRUE
1/12/2017 12:06	C	265	TRUE
1/12/2017 16:17	A	181	TRUE
1/12/2017 16:57	C	88	TRUE
1/12/2017 19:35	A	135	TRUE
1/12/2017 21:26	C	103	TRUE
1/12/2017 23:57	A	153	TRUE
1/13/2017 3:31	A	147	FALSE
1/13/2017 7:45	A	175	FALSE
1/13/2017 7:48	C	164	FALSE
1/13/2017 11:20	A	147	FALSE
1/13/2017 12:32	C	174	FALSE
1/13/2017 15:48	A	151	FALSE
1/13/2017 17:03	C	128	FALSE
1/13/2017 20:06	A	167	FALSE
1/13/2017 21:08	C	155	FALSE
1/13/2017 23:41	A	172	FALSE
1/14/2017 4:33	A	185	FALSE
1/14/2017 7:42	C	140	FALSE
1/14/2017 8:29	A	175	FALSE
1/14/2017 11:42	C	226	FALSE
1/14/2017 11:45	A	146	FALSE
1/14/2017 16:16	A	152	FALSE
1/14/2017 16:57	C	103	FALSE
1/14/2017 21:00	A	164	FALSE
1/14/2017 21:21	C	107	FALSE
1/15/2017 0:26	A	149	TRUE
1/15/2017 4:12	A	134	TRUE
1/15/2017 7:50	C	142	FALSE
1/15/2017 8:05	A	183	TRUE
1/15/2017 11:54	C	242	FALSE
1/15/2017 12:04	A	162	TRUE
1/15/2017 16:35	C	147	FALSE
1/15/2017 16:58	A	163	TRUE
1/15/2017 20:30	C	151	FALSE
1/15/2017 20:44	A	181	TRUE
1/16/2017 0:29	A	136	TRUE
1/16/2017 4:14	A	189	TRUE
1/16/2017 7:46	A	172	TRUE
1/16/2017 7:46	C	59	FALSE
1/16/2017 7:58	C	59	FALSE
1/16/2017 8:16	C	77	FALSE
1/16/2017 11:47	A	165	TRUE
1/16/2017 11:51	C	213	FALSE
1/16/2017 15:27	A	182	TRUE
1/16/2017 17:02	C	63	FALSE
1/16/2017 17:51	C	164	FALSE
1/16/2017 19:47	A	167	TRUE
1/16/2017 20:54	C	61	FALSE
1/16/2017 21:37	C	105	FALSE
1/16/2017 23:57	A	158	TRUE
1/17/2017 4:34	A	161	FALSE
1/17/2017 7:52	C	101	FALSE
1/17/2017 8:30	A	171	FALSE
1/17/2017 11:31	C	209	FALSE
1/17/2017 11:39	A	181	FALSE
1/17/2017 16:31	A	171	FALSE
1/17/2017 17:02	C	96	FALSE
1/17/2017 20:13	A	179	FALSE
1/17/2017 20:59	C	88	FALSE
1/18/2017 0:15	A	167	FALSE
1/18/2017 4:18	A	177	FALSE
1/18/2017 7:36	C	186	FALSE
1/18/2017 8:45	A	176	FALSE
1/18/2017 11:18	A	172	FALSE
1/18/2017 12:06	C	103	FALSE
1/18/2017 16:00	A	185	FALSE
1/18/2017 20:20	A	164	FALSE
1/19/2017 0:18	A	162	FALSE
1/19/2017 3:43	A	157	FALSE
1/19/2017 8:55	A	178	FALSE
1/19/2017 11:24	A	183	FALSE
1/19/2017 16:10	A	132	FALSE
1/19/2017 20:22	A	174	FALSE
1/19/2017 23:49	A	136	FALSE
1/20/2017 4:00	A	158	FALSE
1/20/2017 8:51	A	153	FALSE
1/20/2017 14:11	A	168	FALSE
1/23/2017 13:02	A	180	FALSE
1/24/2017 6:23	A	205	TRUE	A1
1/24/2017 15:24	A	199	TRUE	A1
1/24/2017 21:02	A	197	TRUE	A1
1/25/2017 5:42	A	218	TRUE	A1
1/25/2017 18:03	A	265	TRUE	A1
1/25/2017 21:39	A	208	TRUE	A1
1/26/2017 6:02	A	246	TRUE	A1
1/26/2017 10:11	A	215	TRUE	A1
1/26/2017 14:33	A	238	TRUE	A1
1/26/2017 21:20	A	192	TRUE	A1
1/27/2017 5:54	A	236	TRUE	A1
1/27/2017 9:51	A	215	TRUE	A1
1/27/2017 13:21	A	264	TRUE	A1
1/27/2017 21:46	A	255	TRUE	A1
1/28/2017 5:28	A	252	TRUE	A1
1/28/2017 10:09	A	226	TRUE	A1
1/28/2017 13:33	A	240	TRUE	A1
1/28/2017 21:57	A	230	TRUE	A1
1/29/2017 4:59	A	203	TRUE	A1
1/29/2017 13:00	A	224	TRUE	A1
1/29/2017 22:11	A	201	TRUE	A1
1/30/2017 6:21	A	201	FALSE	<--{=SUM((B237=$B$2:$B$237)*(INT(A237)=INT($A$2:$A$237))*($C$2:$C$237>180))>=2}
Patient		#Occurences
A		3	<-----Should be 1	<--{=SUM(IF(FREQUENCY(IF(($D$2:$D$237=TRUE)*($B$2:$B$237=A240),ROW($D$2:$D$237)),IF(($D$2:$D$237<>TRUE)*($B$2:$B$237=A240),ROW($D$2:$D$237)))>=6,1,0))}
C		2	<-----Should be 1

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>