Hi, i need some help on two matters. both are related to filters, involving dates and multiple column.
a sample of my database as such:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Project[/TD]
[TD]Gender[/TD]
[TD]Type[/TD]
[TD]Funding[/TD]
[TD]Topic[/TD]
[TD]Date of Application[/TD]
[TD]Survey Date 1[/TD]
[TD]Survey Date 2[/TD]
[TD]Survey Date 3[/TD]
[TD]Survey Date 4[/TD]
[TD]Survey Date 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
i need to generate two lists from this database quite frequently and i am hoping to automate it.
the first list is relative simple, i just need to filter gender and date of application, and i am hoping to extract all the corresponding rows into a fresh sheet whenever i key in the date range (at the fresh sheet itself). for example,
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Application Start Date[/TD]
[TD]dd/mm/yyyy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Application End Date[/TD]
[TD]dd/mm/yyyy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Project[/TD]
[TD]Topic[/TD]
[TD]Date of Application[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
the second list is more "complicated". the surveys are done on an adhoc basis so some might be on survey 4 but some might be on their first or not even started yet. i need to filter the rows based on a date range for the survey columns (starting from the second survey onward) that falls between the date range. (is this even possible?)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]12/02/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Date[/TD]
[TD]18/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Project[/TD]
[TD]Gender[/TD]
[TD]Topic[/TD]
[TD]Survey Date 2[/TD]
[TD]Survey Date 3[/TD]
[TD]Survey Date 4[/TD]
[TD]Survey Date 5[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]150/2019[/TD]
[TD]03/03/2019[/TD]
[TD]15/03/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]03/03/2019[/TD]
[TD]04/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]08/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]16/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
or is there any better way to do this? appreciate your help and suggestions on this please!
a sample of my database as such:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Project[/TD]
[TD]Gender[/TD]
[TD]Type[/TD]
[TD]Funding[/TD]
[TD]Topic[/TD]
[TD]Date of Application[/TD]
[TD]Survey Date 1[/TD]
[TD]Survey Date 2[/TD]
[TD]Survey Date 3[/TD]
[TD]Survey Date 4[/TD]
[TD]Survey Date 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
i need to generate two lists from this database quite frequently and i am hoping to automate it.
the first list is relative simple, i just need to filter gender and date of application, and i am hoping to extract all the corresponding rows into a fresh sheet whenever i key in the date range (at the fresh sheet itself). for example,
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Application Start Date[/TD]
[TD]dd/mm/yyyy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Application End Date[/TD]
[TD]dd/mm/yyyy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Project[/TD]
[TD]Topic[/TD]
[TD]Date of Application[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
the second list is more "complicated". the surveys are done on an adhoc basis so some might be on survey 4 but some might be on their first or not even started yet. i need to filter the rows based on a date range for the survey columns (starting from the second survey onward) that falls between the date range. (is this even possible?)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]12/02/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Date[/TD]
[TD]18/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Project[/TD]
[TD]Gender[/TD]
[TD]Topic[/TD]
[TD]Survey Date 2[/TD]
[TD]Survey Date 3[/TD]
[TD]Survey Date 4[/TD]
[TD]Survey Date 5[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]150/2019[/TD]
[TD]03/03/2019[/TD]
[TD]15/03/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]03/03/2019[/TD]
[TD]04/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]08/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]16/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
or is there any better way to do this? appreciate your help and suggestions on this please!