Multiple Criteria search

[masterexcelbundle]

Hello,
This is probably very simple but I just can't figure it out!
Overall I want to know who supervising on the AM and PM shifts over the month by 5 consultants. I have filtered the data to the 'Available' status which is their Supervising status. I however don't want to include anyone who used it under 2hrs as they were probably just temping in and out.

So basically, I need to do a formula where is searches 5 separate columns for a matching date value, then from another 5 columns if duration is under 2hrs, and if other five columns match value "AM", then copy cell in column other columns.

I have uploaded 'DATA SETS1' as where the information is pulled from, using just 2 consultants but there are 3 others.
I have also uploaded 'DATA RESULTS' with the yellow cells how I am hoping the information to be pulled across as, either as AM or PM, also if the person is not listed on that date for the cell to show as blank. (e.g. if neither Adam or Chris worked on the 7th March, the cells under their names would be blank)

Thankyou in advance!

 

[awoohaw]

Where is the link for the upload?
Images really are not a good source for the forum to help you with. PLease use the xl2bb add in tool (link below) to share a mini workbook of you data.

Regarding your images. Where is the raw data that you have created the two data displays in the datasets1.png image?

 

[masterexcelbundle]

Hi,
Unfortunately I can't download the attachment as I am on a work computer and all downloads are blocked.

Raw data is pulled to individual tabs, which I have attached another picture. Grey columns are where I have formatted the data using formulas, white columns are the pulled data. I can only download one agent at a time so they currently have all their own individual tabs.

 

[awoohaw]

post the data as a table then please. Pictures are problematic. It takes time for the people in the forum to recreate your scenario, and they can make typos will doing that. Help the forum help you!

 

[masterexcelbundle]

No worries, I have pasted the data as tables below. Hopefully thats more helpful.

Row Labels	Sum of Duration	Min of Start Time2	Max of End Time	Shift	Leader
1/03/2023	7:10:22​	4:53:00 AM​	1:01:00 PM​	AM	ADAM
6/03/2023	6:13:57​	4:55:00 AM​	1:30:00 PM​	AM	ADAM
7/03/2023	6:06:45​	10:59:00 AM​	7:00:00 PM​	PM	ADAM
8/03/2023	6:45:47​	6:22:00 AM​	2:55:00 PM​	AM	ADAM
9/03/2023	4:02:45​	4:56:00 AM​	10:07:00 AM​	AM	ADAM
10/03/2023	6:23:34​	4:57:00 AM​	1:30:00 PM​	AM	ADAM
11/03/2023	4:20:51​	5:54:00 AM​	12:36:00 PM​	AM	ADAM
12/03/2023	10:00:19​	5:56:00 AM​	4:37:00 PM​	AM	ADAM
13/03/2023	8:31:07​	4:53:00 AM​	2:15:00 PM​	AM	ADAM
14/03/2023	5:00:04​	5:23:00 AM​	1:30:00 PM​	AM	ADAM
20/03/2023	4:29:22​	5:00:00 AM​	11:01:00 AM​	AM	ADAM
24/03/2023	6:28:57​	4:58:00 AM​	1:31:00 PM​	AM	ADAM
25/03/2023	9:44:53​	5:57:00 AM​	4:33:00 PM​	AM	ADAM
26/03/2023	9:46:23​	5:55:00 AM​	4:31:00 PM​	AM	ADAM
27/03/2023	5:47:49​	5:00:00 AM​	1:32:00 PM​	AM	ADAM
28/03/2023	5:13:28​	4:56:00 AM​	1:36:00 PM​	AM	ADAM
29/03/2023	6:06:32​	3:55:00 AM​	12:30:00 PM​	AM	ADAM

Row Labels	Sum of Duration	Min of Start Time2	Max of End Time	Shift	Leader
28/02/2023	0:04:44​	11:58:00 PM​	12:03:00 AM​	PM	CHRIS
1/03/2023	1:54:01​	3:58:00 PM​	12:14:00 AM​	PM	CHRIS
8/03/2023	8:09:32​	2:24:00 PM​	11:47:00 PM​	PM	CHRIS
9/03/2023	0:44:59​	3:58:00 PM​	12:11:00 AM​	PM	CHRIS
10/03/2023	0:29:20​	4:04:00 PM​	11:13:00 PM​	PM	CHRIS
11/03/2023	6:20:04​	1:57:00 PM​	12:08:00 AM​	PM	CHRIS
12/03/2023	8:36:01​	1:55:00 PM​	12:10:00 AM​	PM	CHRIS
24/03/2023	7:36:30​	3:53:00 PM​	12:01:00 AM​	PM	CHRIS
25/03/2023	8:25:22​	11:55:00 AM​	10:09:00 PM​	PM	CHRIS
26/03/2023	0:43:53​	12:01:00 PM​	9:31:00 PM​	PM	CHRIS
28/03/2023	2:35:28​	4:01:00 PM​	11:27:00 PM​	PM	CHRIS
29/03/2023	7:50:12​	3:54:00 PM​	12:15:00 AM​	PM	CHRIS
2/03/2023	1:31:40​	3:56:00 PM​	10:14:00 PM​	PM	CHRIS
15/03/2023	1:23:55​	4:04:00 PM​	12:00:00 AM​	PM	CHRIS
16/03/2023	2:25:57​	12:54:00 PM​	10:18:00 PM​	PM	CHRIS
22/03/2023	1:17:04​	3:55:00 PM​	11:52:00 PM​	PM	CHRIS
23/03/2023	2:12:49​	4:04:00 PM​	12:01:00 AM​	PM	CHRIS
30/03/2023	3:42:50​	12:38:00 PM​	9:15:00 PM​	PM	CHRIS

	ADAM	CHRIS
1/03/2023​	AM - Example	PM - Example
2/03/2023​
3/03/2023​
4/03/2023​
5/03/2023​
6/03/2023​
7/03/2023​
8/03/2023​
9/03/2023​
10/03/2023​
11/03/2023​
12/03/2023​
13/03/2023​
14/03/2023​
15/03/2023​
16/03/2023​
17/03/2023​
18/03/2023​
19/03/2023​
20/03/2023​
21/03/2023​
22/03/2023​
23/03/2023​
24/03/2023​
25/03/2023​
26/03/2023​
27/03/2023​
28/03/2023​
29/03/2023​
30/03/2023​
31/03/2023​

 

[awoohaw]

yes, now the forum can try to figure this out for you.

 

[MEUserII]

No worries, I have pasted the data as tables below. Hopefully thats more helpful.

Row Labels	Sum of Duration	Min of Start Time2	Max of End Time	Shift	Leader
1/03/2023	7:10:22​	4:53:00 AM​	1:01:00 PM​	AM	ADAM
6/03/2023	6:13:57​	4:55:00 AM​	1:30:00 PM​	AM	ADAM
7/03/2023	6:06:45​	10:59:00 AM​	7:00:00 PM​	PM	ADAM
8/03/2023	6:45:47​	6:22:00 AM​	2:55:00 PM​	AM	ADAM
9/03/2023	4:02:45​	4:56:00 AM​	10:07:00 AM​	AM	ADAM
10/03/2023	6:23:34​	4:57:00 AM​	1:30:00 PM​	AM	ADAM
11/03/2023	4:20:51​	5:54:00 AM​	12:36:00 PM​	AM	ADAM
12/03/2023	10:00:19​	5:56:00 AM​	4:37:00 PM​	AM	ADAM
13/03/2023	8:31:07​	4:53:00 AM​	2:15:00 PM​	AM	ADAM
14/03/2023	5:00:04​	5:23:00 AM​	1:30:00 PM​	AM	ADAM
20/03/2023	4:29:22​	5:00:00 AM​	11:01:00 AM​	AM	ADAM
24/03/2023	6:28:57​	4:58:00 AM​	1:31:00 PM​	AM	ADAM
25/03/2023	9:44:53​	5:57:00 AM​	4:33:00 PM​	AM	ADAM
26/03/2023	9:46:23​	5:55:00 AM​	4:31:00 PM​	AM	ADAM
27/03/2023	5:47:49​	5:00:00 AM​	1:32:00 PM​	AM	ADAM
28/03/2023	5:13:28​	4:56:00 AM​	1:36:00 PM​	AM	ADAM
29/03/2023	6:06:32​	3:55:00 AM​	12:30:00 PM​	AM	ADAM

Row Labels	Sum of Duration	Min of Start Time2	Max of End Time	Shift	Leader
28/02/2023	0:04:44​	11:58:00 PM​	12:03:00 AM​	PM	CHRIS
1/03/2023	1:54:01​	3:58:00 PM​	12:14:00 AM​	PM	CHRIS
8/03/2023	8:09:32​	2:24:00 PM​	11:47:00 PM​	PM	CHRIS
9/03/2023	0:44:59​	3:58:00 PM​	12:11:00 AM​	PM	CHRIS
10/03/2023	0:29:20​	4:04:00 PM​	11:13:00 PM​	PM	CHRIS
11/03/2023	6:20:04​	1:57:00 PM​	12:08:00 AM​	PM	CHRIS
12/03/2023	8:36:01​	1:55:00 PM​	12:10:00 AM​	PM	CHRIS
24/03/2023	7:36:30​	3:53:00 PM​	12:01:00 AM​	PM	CHRIS
25/03/2023	8:25:22​	11:55:00 AM​	10:09:00 PM​	PM	CHRIS
26/03/2023	0:43:53​	12:01:00 PM​	9:31:00 PM​	PM	CHRIS
28/03/2023	2:35:28​	4:01:00 PM​	11:27:00 PM​	PM	CHRIS
29/03/2023	7:50:12​	3:54:00 PM​	12:15:00 AM​	PM	CHRIS
2/03/2023	1:31:40​	3:56:00 PM​	10:14:00 PM​	PM	CHRIS
15/03/2023	1:23:55​	4:04:00 PM​	12:00:00 AM​	PM	CHRIS
16/03/2023	2:25:57​	12:54:00 PM​	10:18:00 PM​	PM	CHRIS
22/03/2023	1:17:04​	3:55:00 PM​	11:52:00 PM​	PM	CHRIS
23/03/2023	2:12:49​	4:04:00 PM​	12:01:00 AM​	PM	CHRIS
30/03/2023	3:42:50​	12:38:00 PM​	9:15:00 PM​	PM	CHRIS

	ADAM	CHRIS
1/03/2023​	AM - Example	PM - Example
2/03/2023​
3/03/2023​
4/03/2023​
5/03/2023​
6/03/2023​
7/03/2023​
8/03/2023​
9/03/2023​
10/03/2023​
11/03/2023​
12/03/2023​
13/03/2023​
14/03/2023​
15/03/2023​
16/03/2023​
17/03/2023​
18/03/2023​
19/03/2023​
20/03/2023​
21/03/2023​
22/03/2023​
23/03/2023​
24/03/2023​
25/03/2023​
26/03/2023​
27/03/2023​
28/03/2023​
29/03/2023​
30/03/2023​
31/03/2023​

What does the column 'sum of duration' represent as I noticed this column does not match the difference in time between: Min of Start Time and Max of End Time?

 

[awoohaw]

ok, try this:

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Row Labels	Sum of Duration	Min of Start Time2	Max of End Time	Shift	Leader			ADAM	CHRIS
2	2023-03-01	07:10:22	04:53:00	13:01:00	AM	ADAM		2023-03-01	AM	PM
3	2023-03-06	06:13:57	04:55:00	13:30:00	AM	ADAM		2023-03-02		PM
4	2023-03-07	06:06:45	10:59:00	19:00:00	PM	ADAM		2023-03-03
5	2023-03-08	06:45:47	06:22:00	14:55:00	AM	ADAM		2023-03-04
6	2023-03-09	04:02:45	04:56:00	10:07:00	AM	ADAM		2023-03-05
7	2023-03-10	06:23:34	04:57:00	13:30:00	AM	ADAM		2023-03-06	AM
8	2023-03-11	04:20:51	05:54:00	00:36:00	AM	ADAM		2023-03-07	PM
9	2023-03-12	10:00:19	05:56:00	16:37:00	AM	ADAM		2023-03-08	AM	PM
10	2023-03-13	08:31:07	04:53:00	14:15:00	AM	ADAM		2023-03-09	AM	PM
11	2023-03-14	05:00:04	05:23:00	13:30:00	AM	ADAM		2023-03-10	AM	PM
12	2023-03-20	04:29:22	05:00:00	11:01:00	AM	ADAM		2023-03-11	AM	PM
13	2023-03-24	06:28:57	04:58:00	13:31:00	AM	ADAM		2023-03-12	AM	PM
14	2023-03-25	09:44:53	05:57:00	16:33:00	AM	ADAM		2023-03-13	AM
15	2023-03-26	09:46:23	05:55:00	16:31:00	AM	ADAM		2023-03-14	AM
16	2023-03-27	05:47:49	05:00:00	13:32:00	AM	ADAM		2023-03-15		PM
17	2023-03-28	05:13:28	04:56:00	13:36:00	AM	ADAM		2023-03-16		PM
18	2023-03-29	06:06:32	03:55:00	00:30:00	AM	ADAM		2023-03-17
19	2023-02-28	00:04:44	23:58:00	12:03:00	PM	CHRIS		2023-03-18
20	2023-03-01	01:54:01	15:58:00	12:14:00	PM	CHRIS		2023-03-19
21	2023-03-08	08:09:32	14:24:00	23:47:00	PM	CHRIS		2023-03-20	AM
22	2023-03-09	00:44:59	15:58:00	12:11:00	PM	CHRIS		2023-03-21
23	2023-03-10	00:29:20	16:04:00	23:13:00	PM	CHRIS		2023-03-22		PM
24	2023-03-11	06:20:04	13:57:00	12:08:00	PM	CHRIS		2023-03-23		PM
25	2023-03-12	08:36:01	13:55:00	12:10:00	PM	CHRIS		2023-03-24	AM	PM
26	2023-03-24	07:36:30	15:53:00	12:01:00	PM	CHRIS		2023-03-25	AM	PM
27	2023-03-25	08:25:22	11:55:00	22:09:00	PM	CHRIS		2023-03-26	AM	PM
28	2023-03-26	00:43:53	00:01:00	21:31:00	PM	CHRIS		2023-03-27	AM
29	2023-03-28	02:35:28	16:01:00	23:27:00	PM	CHRIS		2023-03-28	AM	PM
30	2023-03-29	07:50:12	15:54:00	12:15:00	PM	CHRIS		2023-03-29	AM	PM
31	2023-03-02	01:31:40	15:56:00	22:14:00	PM	CHRIS		2023-03-30		PM
32	2023-03-15	01:23:55	16:04:00	12:00:00	PM	CHRIS		2023-03-31
33	2023-03-16	02:25:57	00:54:00	22:18:00	PM	CHRIS
34	2023-03-22	01:17:04	15:55:00	23:52:00	PM	CHRIS
35	2023-03-23	02:12:49	16:04:00	12:01:00	PM	CHRIS
36	2023-03-30	03:42:50	00:38:00	21:15:00	PM	CHRIS
masterexcelbundle

Cell Formulas
Range	Formula
I2:J36	I2	=IFERROR(INDEX($E$2:$E$36,MATCH($H2&I$1,$A$2:$A$36&$F$2:$F$36,0)),"")

 

[awoohaw]

What does the column 'sum of duration' represent as I noticed this column does not match the difference in time between: Min of Start Time and Max of End Time?

@MEUserII , I think there are some extraneous columns. From what I gather he only wants to get what shift people are on per day. The column in question may be on whether they have two hours of time or not, but that is my next step in the process.

 

[awoohaw]

I think this checks for shifts of 2hrs or longer:
had an error getting it corrected:
updated

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Row Labels	Sum of Duration	Min of Start Time2	Max of End Time	Shift	Leader			ADAM	CHRIS
2	2023-03-01	07:10:22	04:53:00	13:01:00	AM	ADAM		2023-03-01	AM	PM
3	2023-03-06	06:13:57	04:55:00	13:30:00	AM	ADAM		2023-03-02		PM
4	2023-03-07	06:06:45	10:59:00	19:00:00	PM	ADAM		2023-03-03
5	2023-03-08	06:45:47	06:22:00	14:55:00	AM	ADAM		2023-03-04
6	2023-03-09	04:02:45	04:56:00	10:07:00	AM	ADAM		2023-03-05
7	2023-03-10	06:23:34	04:57:00	13:30:00	AM	ADAM		2023-03-06	AM
8	2023-03-11	04:20:51	05:54:00	00:36:00	AM	ADAM		2023-03-07	PM
9	2023-03-12	10:00:19	05:56:00	16:37:00	AM	ADAM		2023-03-08	AM	PM
10	2023-03-13	08:31:07	04:53:00	14:15:00	AM	ADAM		2023-03-09	AM	PM
11	2023-03-14	05:00:04	05:23:00	13:30:00	AM	ADAM		2023-03-10	AM	PM
12	2023-03-20	04:29:22	05:00:00	11:01:00	AM	ADAM		2023-03-11	AM	PM
13	2023-03-24	06:28:57	04:58:00	13:31:00	AM	ADAM		2023-03-12	AM	PM
14	2023-03-25	09:44:53	05:57:00	16:33:00	AM	ADAM		2023-03-13	AM
15	2023-03-26	09:46:23	05:55:00	16:31:00	AM	ADAM		2023-03-14	AM
16	2023-03-27	05:47:49	05:00:00	13:32:00	AM	ADAM		2023-03-15		PM
17	2023-03-28	05:13:28	04:56:00	13:36:00	AM	ADAM		2023-03-16		PM
18	2023-03-29	06:06:32	03:55:00	00:30:00	AM	ADAM		2023-03-17
19	2023-02-28	00:04:44	23:58:00	12:03:00	PM	CHRIS		2023-03-18
20	2023-03-01	01:54:01	15:58:00	12:14:00	PM	CHRIS		2023-03-19
21	2023-03-08	08:09:32	14:24:00	23:47:00	PM	CHRIS		2023-03-20	AM
22	2023-03-09	00:44:59	15:58:00	12:11:00	PM	CHRIS		2023-03-21
23	2023-03-10	00:29:20	16:04:00	23:13:00	PM	CHRIS		2023-03-22
24	2023-03-11	06:20:04	13:57:00	12:08:00	PM	CHRIS		2023-03-23		PM
25	2023-03-12	08:36:01	13:55:00	12:10:00	PM	CHRIS		2023-03-24	AM	PM
26	2023-03-24	07:36:30	15:53:00	12:01:00	PM	CHRIS		2023-03-25	AM	PM
27	2023-03-25	08:25:22	11:55:00	22:09:00	PM	CHRIS		2023-03-26	AM	PM
28	2023-03-26	00:43:53	00:01:00	21:31:00	PM	CHRIS		2023-03-27
29	2023-03-28	02:35:28	16:01:00	23:27:00	PM	CHRIS		2023-03-28	AM	PM
30	2023-03-29	07:50:12	15:54:00	12:15:00	PM	CHRIS		2023-03-29	AM	PM
31	2023-03-02	01:31:40	15:56:00	22:14:00	PM	CHRIS		2023-03-30
32	2023-03-15	01:23:55	16:04:00	12:00:00	PM	CHRIS		2023-03-31
33	2023-03-16	02:25:57	00:54:00	22:18:00	PM	CHRIS
34	2023-03-22	01:17:04	15:55:00	23:52:00	PM	CHRIS
35	2023-03-23	02:12:49	16:04:00	12:01:00	PM	CHRIS
36	2023-03-30	03:42:50	00:38:00	21:15:00	PM	CHRIS
masterexcelbundle

Cell Formulas
Range	Formula
I2:J36	I2	=IF($B2>=TIME(2,0,0),IFERROR(INDEX($E$2:$E$36,MATCH($H2&I$1,$A$2:$A$36&$F$2:$F$36,0)),""),"")