Did an advanced filter with multiple criteria but how do i delete those cells that don't fall within the criteria?

[stanco]

the purpose of this filter is to find companies that have done survey 2-6 within the date range (28 feb till 31 dec in this case). but it copy the entire row and shows results that falls outside of this date range as well.

is there any way to use vba to delete those cells that falls outside of the criteria?

and, am i doing the advanced filter correctly? or is there an easier way to do what i want?

 

[stanco]

Up for suggestions...

 

[John_w]

You probably aren't using the Advanced Filter correctly. There shouldn't be any empty cells between the column headings and the criteria. As shown, the Survey 2 Date criteria are in the correct cells, but the Survey 3, 4, 5 and 6 criteria should be moved directly below their column headings. Then you can do an Advanced Filter using the Survey 2 Date column headings and criteria (select all 4 cells for the criteria range), then another Advanced Filter on the result, using the Survey 3 Date column headings and criteria, and so on for the other Surveys.

 

[stanco]

You probably aren't using the Advanced Filter correctly. There shouldn't be any empty cells between the column headings and the criteria. As shown, the Survey 2 Date criteria are in the correct cells, but the Survey 3, 4, 5 and 6 criteria should be moved directly below their column headings. Then you can do an Advanced Filter using the Survey 2 Date column headings and criteria (select all 4 cells for the criteria range), then another Advanced Filter on the result, using the Survey 3 Date column headings and criteria, and so on for the other Surveys.

i thought the blanks in the criteria for survey 3-6 means OR. If i remove the blanks, it would meant that it would need to meet the criteria of survey 2 and survey 3 and survey 4 and etc. which will return no results.

i am hoping it would be a 1 step filter instead of doing the filtering by surveys individually, and then i still need to compare the same company who has done two or more surveys within the same date range to take the later date (if any, among the list).

do you have any other suggestions for me? basically i need to find the companies who have completed the survey within the date range, and i only need the latest date if a company has done two or more surveys within the same date range.

 

[John_w]

It wasn't clear, but I read your post as wanting AND criteria for all surveys.

Maybe a helper column would work if you need OR criteria. Something like:

=OR( AND(G11>=$A$4,G11<=$B$4), AND(H11>=$A$4,H11<=$B$4), AND(I11>=$A$4,I11<=$B$4), AND(J11>=$A$4,J11<=$B$4) )

where A4 is the start date and B4 is the end date, checking 4 surveys - add more ANDs as required. Then AutoFilter on the helper column being TRUE.