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

 

[melifreeman]

Sorry - one tweak...I need it to leave blank cells as blank cells. It's defaulting to "NO" in the empty cells...

 

[Fluff]

Blanks cells will not default to NO, but they will contain 0.

 

[melifreeman]

Blanks cells will not default to NO, but they will contain

Is there any way to keep the cell blank?

 

[Fluff]

How about

=if(isblank(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")))),"",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]

How about

=if(isblank(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")))),"",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"))))

Thank you!!

 

[Peter_SSs]

Another way?

=FILTER(IF(ISNUMBER(A2:J18),A2:J18,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]

Well, in my test page the filter formula worked, but when I try to put it into the actual document it is not filtering out the site ID. I can get the dates and data to pull across but the filter is missing the specific site...here is the formula and what I'm getting in return:

=FILTER('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$J$15,('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$A$15=DATEVALUE("3/16/2021")+('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$A$15=DATEVALUE("3/17/2021"))*('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$B$2:$B$15="MEP-02")))

DATE	SITE ID	SCHEDULED TIME	SELECTEE LAST NAME	SELECTEE FIRST NAME	HRS	PHOTO REQ.	COMPLETE	NO SHOW	OTHER REASON FOR NON-COMPLETION
16-Mar	MEP-01	1:25 PM	BUNNY	BUGS	7790103	YES	NO	0	NO ID
16-Mar	MEP-02	12:00 AM	STARK	TONY	8678989	NO	YES	0	-
16-Mar	MEP-02	9:15 AM	BUNNY	BUGS	4567890	YES	NO	0	NO ID
16-Mar	MEP-04	12:00 AM	FUDD	ELMER	6012331	YES	YES	0	-
16-Mar	MEP-01	2:15 PM	FUDD	ELMER	3456789	YES	YES	0	-
16-Mar	MEP-01	1:30 PM	BANNER	BRUCE	2345678	YES	NO	YES	-
17-Mar	MEP-02	12:00 AM	BANNER	BRUCE	6901217	YES	NO	YES	-

What am I doing wrong or missing??

 

[Fluff]

Try

=FILTER('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$J$15,(('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$A$15=DATEVALUE("3/16/2021"))+('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$A$15=DATEVALUE("3/17/2021")))*('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$B$2:$B$15="MEP-02"))

 

[melifreeman]

Try

=FILTER('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$J$15,(('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$A$15=DATEVALUE("3/16/2021"))+('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$A$2:$A$15=DATEVALUE("3/17/2021")))*('[OY3 Selectee Schedules - Completion Log.xlsx]Sheet1'!$B$2:$B$15="MEP-02"))

That helped with pulling the specifics for MEP-02, but when I try to add in another site ID it gives me a calc issue. For instance, I need it to pull the dates listed (which it did beautifully) for MEP-01 AND MEP-02, but not the other sites. Or I can get it to pull the site IDs but then it doesn't filter the dates and gives me other dates with the same site IDs.

 

[Fluff]

What did you try to get both sites?