thelittleredfox
New Member
- Joined
- Dec 15, 2014
- Messages
- 17
Hi,
I have the following spreadsheet, which lists all payments made under client accounts by date. (Please excuse the horrible layout).
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Row number
[/TD]
[TD]Client Name
[/TD]
[TD]Payment Date
[/TD]
[TD]Payment Amount
[/TD]
[TD]True or False
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]2[/TD]
[TD]Dominic Holden[/TD]
[TD] 20/04/2018[/TD]
[TD]£6.58[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]3[/TD]
[TD]Gary Baines[/TD]
[TD]02/06/2018[/TD]
[TD]£11.22[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]5[/TD]
[TD]Teri Kane[/TD]
[TD]05/09/2018[/TD]
[TD] £16.73[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]6[/TD]
[TD]Julian Baker[/TD]
[TD]23/09/2018[/TD]
[TD]£9.31[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]7[/TD]
[TD]Gillian Houghton[/TD]
[TD]26/09/2018[/TD]
[TD]£8.10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]8[/TD]
[TD]Thomas Hunt[/TD]
[TD]14/10/2018[/TD]
[TD]£7.99[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]9[/TD]
[TD]James Clarke[/TD]
[TD]28/10/2018[/TD]
[TD]£2.56[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
The sheet is called "data sheet" and currently has 5000 lines of data, which will probably increase to around 10,000 by the end of the year.
What I would like to do is to create a new tab which lists all records which fall within a specified time period AND where the value in column E is TRUE. There might be multiple results for the same date where column E is TRUE. I'd like it to look something like this:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Client Name
[/TD]
[TD]Payment Date
[/TD]
[TD]Payment Amount
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Julian Baker[/TD]
[TD]23/09/2018[/TD]
[TD]£9.31[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gillian Houghton[/TD]
[TD]26/09/2018[/TD]
[TD]£8.10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]James Clarke[/TD]
[TD]28/10/2018[/TD]
[TD]£2.56[/TD]
[/TR]
</tbody>[/TABLE]
I'm guessing I need to use an array formula but I've tried and just can't manage to create one that works. Any help would be appreciated!
I have the following spreadsheet, which lists all payments made under client accounts by date. (Please excuse the horrible layout).
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Row number
[/TD]
[TD]Client Name
[/TD]
[TD]Payment Date
[/TD]
[TD]Payment Amount
[/TD]
[TD]True or False
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]2[/TD]
[TD]Dominic Holden[/TD]
[TD] 20/04/2018[/TD]
[TD]£6.58[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]3[/TD]
[TD]Gary Baines[/TD]
[TD]02/06/2018[/TD]
[TD]£11.22[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]5[/TD]
[TD]Teri Kane[/TD]
[TD]05/09/2018[/TD]
[TD] £16.73[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]6[/TD]
[TD]Julian Baker[/TD]
[TD]23/09/2018[/TD]
[TD]£9.31[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]7[/TD]
[TD]Gillian Houghton[/TD]
[TD]26/09/2018[/TD]
[TD]£8.10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]8[/TD]
[TD]Thomas Hunt[/TD]
[TD]14/10/2018[/TD]
[TD]£7.99[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]9[/TD]
[TD]James Clarke[/TD]
[TD]28/10/2018[/TD]
[TD]£2.56[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
The sheet is called "data sheet" and currently has 5000 lines of data, which will probably increase to around 10,000 by the end of the year.
What I would like to do is to create a new tab which lists all records which fall within a specified time period AND where the value in column E is TRUE. There might be multiple results for the same date where column E is TRUE. I'd like it to look something like this:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Client Name
[/TD]
[TD]Payment Date
[/TD]
[TD]Payment Amount
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Julian Baker[/TD]
[TD]23/09/2018[/TD]
[TD]£9.31[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gillian Houghton[/TD]
[TD]26/09/2018[/TD]
[TD]£8.10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]James Clarke[/TD]
[TD]28/10/2018[/TD]
[TD]£2.56[/TD]
[/TR]
</tbody>[/TABLE]
I'm guessing I need to use an array formula but I've tried and just can't manage to create one that works. Any help would be appreciated!