Formula to extract entire row based on specific criteria

[melifreeman]

Hi All,

You were a great help with my last issue so I'm hoping for some more assistance. I have a LARGE project coming up where I will need to track multiple sites and data. I'm hoping to find a way to extract data from one master spreadsheet of information that uses a date range AND a multiple site IDs to fill in the row of data. Below is a mock-up of the master layout. Basically I need it to pull like this:

If the date is between 2/16/21 and 2/18/21 AND the SITE ID includes SDF-03, ABC-01 and ADF-09.....data fills across the row

I can get the data to pull for the Site IDs but the second I try to put in a date range too both my formula (and my brain) goes off track. Any ideas??? Thanks in advance!

DATE	SITE ID	SCHEDULED TIME	LAST NAME	FIRST NAME	ID	PHOTO REQ.	COMPLETE	NO SHOW	OTHER REASON FOR NON-COMPLETION
16-Feb	SDF-03		BUNNY	BUGS	7790103	YES	NO		NO ID
16-Feb	ABC-01		STARK	TONY	8678989	NO	YES
16-Feb	ABC-02		FUDD	ELMER	9567875	YES	YES
17-Feb	ADF-09		BANNER	BRUCE	10456760	YES	NO	YES
17-Feb	ADF-09		BUNNY	BUGS	11345646	YES	NO		NO ID
18-Feb	ABC-01		STARK	TONY	12234532	NO	YES
16-Feb	ABC-02		FUDD	ELMER	13123418	YES	YES
16-Feb	ABC-01	1:01 PM	STARK	TONY	1234567	NO	YES
18-Feb	SDF-03	2:15 PM	FUDD	ELMER	3456789	YES	YES
18-Feb	ADF-09	1:30 PM	BANNER	BRUCE	2345678	YES	NO	YES
16-Feb	SDF-02	9:15 AM	BUNNY	BUGS	4567890	YES	NO		NO ID
17-Feb	ABC-01		STARK	TONY	5123446	NO	YES
17-Feb	ABC-02		FUDD	ELMER	6012331	YES	YES
16-Feb	ABC-01		BANNER	BRUCE	6901217	YES	NO	YES

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	SITE ID	SCHEDULED TIME	LAST NAME	FIRST NAME	ID	PHOTO REQ.	COMPLETE	NO SHOW	OTHER REASON FOR NON-COMPLETION
2	16/02/2021	SDF-03		BUNNY	BUGS	7790103	YES	NO		NO ID
3	16/02/2021	ABC-01		STARK	TONY	8678989	NO	YES
4	16/02/2021	ABC-02		FUDD	ELMER	9567875	YES	YES
5	17/02/2021	ADF-09		BANNER	BRUCE	10456760	YES	NO	YES
6	17/02/2021	ADF-09		BUNNY	BUGS	11345646	YES	NO		NO ID
7	18/02/2021	ABC-01		STARK	TONY	12234532	NO	YES
8	16/02/2021	ABC-02		FUDD	ELMER	13123418	YES	YES
9	16/02/2021	ABC-01	1:01 PM	STARK	TONY	1234567	NO	YES
10	18/02/2021	SDF-03	2:15 PM	FUDD	ELMER	3456789	YES	YES
11	18/02/2021	ADF-09	1:30 PM	BANNER	BRUCE	2345678	YES	NO	YES
12	16/02/2021	SDF-02	9:15 AM	BUNNY	BUGS	4567890	YES	NO		NO ID
13	17/02/2021	ABC-01		STARK	TONY	5123446	NO	YES
14	17/02/2021	ABC-02		FUDD	ELMER	6012331	YES	YES
15	16/02/2021	ABC-01		BANNER	BRUCE	6901217	YES	NO	YES
16
17
18
19
20	16/02/2021	SDF-03
21	18/02/2021	ABC-01
22		ADF-09
23	DATE	SITE ID	SCHEDULED TIME	LAST NAME	FIRST NAME	ID	PHOTO REQ.	COMPLETE	NO SHOW	OTHER REASON FOR NON-COMPLETION
24	16/02/2021	SDF-03		BUNNY	BUGS	7790103	YES	NO		NO ID
25	16/02/2021	ABC-01		STARK	TONY	8678989	NO	YES
26	17/02/2021	ADF-09		BANNER	BRUCE	10456760	YES	NO	YES
27	17/02/2021	ADF-09		BUNNY	BUGS	11345646	YES	NO		NO ID
28	18/02/2021	ABC-01		STARK	TONY	12234532	NO	YES
29	16/02/2021	ABC-01	1:01:00 PM	STARK	TONY	1234567	NO	YES
30	18/02/2021	SDF-03	2:15:00 PM	FUDD	ELMER	3456789	YES	YES
31	18/02/2021	ADF-09	1:30:00 PM	BANNER	BRUCE	2345678	YES	NO	YES
32	17/02/2021	ABC-01		STARK	TONY	5123446	NO	YES
33	16/02/2021	ABC-01		BANNER	BRUCE	6901217	YES	NO	YES
34
Data

Cell Formulas
Range	Formula
A24:J33	A24	=IF(ISBLANK(FILTER(A2:J18,(A2:A18>=A20)*(A2:A18<=A21)*(COUNTIF(B20:B22,B2:B18)))),"",FILTER(A2:J18,(A2:A18>=A20)*(A2:A18<=A21)*(COUNTIF(B20:B22,B2:B18))))
Dynamic array formulas.

 

[melifreeman]

This looks somewhat like what I'm hoping for. However, I need to be able to specify the Site IDs for it to look for (these are just samples, but I have roughly 600 ID numbers. Each corresponding to their own tab in the workbook). When I type it out it's giving me a #CALC error...There is no easy way to formulate the date range AND the specific site ID range is there? :/

 

[Peter_SSs]

I need to be able to specify the Site IDs for it to look for

That is what Fluff has in cells B20:B22.
His formula has extracted all information from 16 Feb to 18 Feb for those three site ID's. In what way is that not meeting your request ..

If the date is between 2/16/21 and 2/18/21 AND the SITE ID includes SDF-03, ABC-01 and ADF-09.....data fills across the row

 

[melifreeman]

The site IDs and date ranges need to be typed in manually ahead of time so that I don't have to go into each one daily. I can't just have it selecting specific cells as the main data file isn't complete, it's a live report that comes out every morning. Therefore, I need to be able to set the formula up to watch the full array for the specified date ranges and site IDs without referring to a specific cell. I hope that makes sense.

 

[Fluff]

If you don't use cells to specify the date range & site ID's like I did in post#2 with A20:A2 & B20:B22, then how will the formula know what to look for?

 

[melifreeman]

If you don't use cells to specify the date range & site ID's like I did in post#2 with A20:A2 & B20:B22, then how will the formula know what to look for?

I have the date range and the site ids, but NOT in the master file. That information won't get added until the morning of. The dates and locations are set, but the information for the people arriving and the data for each does not get populated until the morning of their appointment. I can enter the data into the formula of what I need it to look for, but I can't specify a cell as it could be cell A 32 or it could be cell A64, etc.

 

[Fluff]

How about

=FILTER(A2:J18,(A2:A18>=DATEVALUE("16/02/2021"))*(A2:A18<=DATEVALUE("18/02/2021"))*((B2:B18="SDF-03")+(B2:B18="abc-01")+(B2:B18="adf-09")))

 

[melifreeman]

That worked! Thank you much!!!

 

[Fluff]

You're welcome & thanks for the feedback.