VBA/Advanced Filter with parameters...any advice appreciated!

[Maggie Barr]

Hello, and thank you in advance for taking the time to look over this request.
I am working on a PC in excel 2013, and I have a large data set/sets that I need to flag (or produce in a separate sheet) data that does not fit into several categories. I have a list of bird species with observation dates and breeding codes. From that, I need two things, first to flag (or produce a separate sheet of) records of certain codes that fall outside the breeding windows and, second, all records that have breeding codes that are not likely appropriate for the bird. I am trying to figure out what method would best/most efficiently produce the results I need.

I have three spreadsheets. The first one is where each species has its own breeding window, Column A is the species, Column B is the window (month/day – month/day). The second one is a bit trickier, in that it has Column A is the species list, while the breeding codes are in Row 2, with all the acceptable numbers for the codes in the cells for each species. The third one, the test data, I need to filter/vba/querry out species with the breeding codes of S, H, S7, M, P, and T that have those codes outside the breeding window from sheet1, and in another filter/vba/querry, filter out all the species that have codes in sheet 3 that are numbered a 3 or a 4 in our code sheet, sheet 2. This I think, would be a two tiered filtering process to produce two sets of data for review. If anyone can provide any help or guidance as to what they believe the best method for me to pursue, I would greatly appreciate it. I am not a VBA writer unfortunately, and I do not think running filters in tables will be efficient. I am hoping to find a way to do this in excel since our data is already there, and we will be doing this quite often. Below is an example of what the data looks like.
Thank you in advance for any help you may be able to provide.
Sincerely,
Maggie
Sheet one looks like:

Species	Safe Date Window
Species 1	1/25 - 8/1
Species 2	3/1 - 7/1
Species 3	3/1 - 8/25
Species 4	3/1 - 11/1
Species 5	3/15 - 7/25
Species 6	3/16 - 7/31
Species 7	3/25 - 7/1
Species 8	3/25 - 7/15
Species 9	4/1 - 7/1
Species 10	4/1 - 7/20
Species 11	4/1 - 8/1
Species 12	4/1 - 8/5

<tbody>
</tbody>

Sheet 2 Looks like:

Species
H	S	S7	M	P	T	C	N	A	B	PE	CN	NB	DD	UN	ON	FL	CF	FY	FS	NE	NY
Species 1	4	4	4	4	3	2	2	4	4	4	3	4	4	4	4	4	3	3	3	4	4	4
Species 2	1	1	1	2	1	1	1	1	3	4	4	2	3	4	4	4	4	1	1	2	3	3
Species 3	3	4	4	4	4	3	3	2	2	2	3	4	3	4	4	1	3	3	3	4	1	2
Species 4	1	4	4	4	2	4	4	1	4	4	4	4	4	4	4	1	4	1	1	4	3	3
Species 5	2	2	2	3	2	3	3	3	3	4	3	1	1	4	4	1	3	2	1	2	1	1
Species 6	1	4	4	4	2	4	4	1	4	4	4	4	4	4	4	1	4	1	1	4	3	3
Species 7	3	3	3	4	3	3	3	3	3	4	4	3	3	4	4	3	3	3	3	3	3	3
Species 8	1	4	4	4	2	2	2	3	2	4	3	4	4	3	3	1	1	4	4	4	2	3
Species 9	1	4	4	4	2	4	4	1	4	4	4	4	4	4	4	1	4	1	1	4	3	3
Species 10	2	4	4	4	2	2	1	1	2	4	3	1	1	4	4	1	1	3	1	2	3	1
Species 11	2	1	1	1	2	2	2	2	1	4	3	1	2	4	4	2	2	1	1	4	1	1
Species 12	3	4	4	4	3	4	3	3	4	4	4	3	3	4	4	3	3	4	3	4	3	3

<tbody>
[TD="colspan: 2"] Possible
[/TD]
[TD="colspan: 8"] Probable
[/TD]
[TD="colspan: 12"] Confirmed
[/TD]

</tbody>

The test Data looks like this:

COMMON NAME	BREEDING CODE	OBSERVATION DATE
Species 1	P	6/1/2017
Species 2	S	6/1/2017
Species 3	S	6/1/2017
Species 4	S	6/1/2017
Species 5	C	6/1/2017
Species 6	C	6/1/2017
Species 7	C	6/1/2017
Species 8	C	6/1/2017
Species 9	C	6/1/2017
Species 10	S	6/1/2017
Species 11	S	6/1/2017
Species 12	H	6/1/2017
Species 13	S	6/1/2017
Species 14	M	6/1/2017
Species 15	H	6/1/2017
Species 16	H	6/1/2017
Species 17	H	6/1/2017
Species 18	H	6/1/2017
Species 19	S	6/1/2017
Species 20	S	6/1/2017
Species 21	NY	6/1/2017
Species 22	H	6/1/2017
Species 23	F	6/1/2017
Species 24	FY	6/1/2017
Species 25	S	6/1/2017
Species 26	S	6/1/2017

<tbody>
</tbody>

 

[Maggie Barr]

UPDATE:
I did not find a way to edit/delete my initial thread, so this is an update. I figured out how to get the species recorded with unacceptable codes by refining sheet 2 to to a list of species in Column A and a list of unacceptable codes in Column B and then combined the two in Column D, then combined the same data in sheet 3 (the records), and then ran a MYVLOOKUP to return multiple finds in one cell for each of the species (I actually had it return the observation number for the record). Not the easiest way, but fairly efficient and it works.

I now would like to know if anyone can tell me how, in excel, one can search a set of data for records that have a date that falls outside a date range in another sheet. It doesn't sound that difficult, but I have a different date range for each species, so I need to link the range to the name in the search. The date range format is Month/Day - Month/Day, and the specific date to see if it falls outside the range is Month/Day (or at least I will make it that way because year is irrelevant here).

Any advice would be greatly appreciated.
Thank you,
Maggie