I have a data set below. What I would like to do is use the filter array function and produce an output of the Book Date, Ck #, Description, Amount and Bank Date columns using criteria for the Book Date, Check Date and Bank Date fields to filter the rows. For example, filter the rows with Book Date less than or equal to a date with the range name os_date, Ck # greater than 0 and Bank Date greater than the date in the range named os_date. The range os_date is equal to 03/31/20. The output would look like the second data set below.
Test File.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Book Date | Ck # | Description | Amount | Balance | Balance | Bank Date | ||
2 | 01/01/20 | xxxxxxxxxxxx | $ 46,777.77 | 01/01/20 | |||||
3 | 01/31/20 | xxxxxxxxxxxx | $ 1.19 | $ 46,778.96 | $ 46,778.96 | 01/31/20 | |||
4 | 02/14/20 | 1496 | xxxxxxxxxxxx | $ (10.00) | $ 46,768.96 | 02/28/20 | |||
5 | 02/14/20 | 1497 | xxxxxxxxxxxx | $ (150.00) | $ 46,618.96 | 03/03/20 | |||
6 | 02/29/20 | xxxxxxxxxxxx | $ 1.11 | $ 46,620.07 | $ 46,770.07 | 02/29/20 | |||
7 | 03/31/20 | 1498 | xxxxxxxxxxxx | $ (10,459.00) | $ 36,161.07 | 05/11/20 | |||
8 | 03/31/20 | 1499 | xxxxxxxxxxxx | $ (5,000.00) | $ 31,161.07 | 04/28/20 | |||
9 | 03/31/20 | 1500 | xxxxxxxxxxxx | $ (10,000.00) | $ 21,161.07 | 05/22/20 | |||
10 | 03/31/20 | 1501 | xxxxxxxxxxxx | $ (5,000.00) | $ 16,161.07 | 05/21/20 | |||
11 | 03/31/20 | 1502 | xxxxxxxxxxxx | $ (5,000.00) | $ 11,161.07 | 06/12/20 | |||
12 | 03/31/20 | 1503 | xxxxxxxxxxxx | $ (5,000.00) | $ 6,161.07 | 06/12/20 | |||
13 | 03/31/20 | 1504 | xxxxxxxxxxxx | $ (5,000.00) | $ 1,161.07 | 07/31/20 | |||
14 | 03/31/20 | 1505 | xxxxxxxxxxxx | $ (5,000.00) | $ (3,838.93) | 06/12/20 | |||
15 | 03/31/20 | xxxxxxxxxxxx | $ 0.84 | $ (3,838.09) | $ 46,620.91 | 03/31/20 | |||
16 | 04/17/20 | 1506 | xxxxxxxxxxxx | $ (1,000.00) | $ (4,838.09) | 04/27/20 | |||
17 | 04/02/20 | xxxxxxxxxxxx | $ 50,459.00 | $ 45,620.91 | 04/02/20 | ||||
18 | 04/30/20 | xxxxxxxxxxxx | $ 1.54 | $ 45,622.45 | $ 91,081.45 | 04/30/20 | |||
19 | 05/31/20 | xxxxxxxxxxxx | $ 1.34 | $ 45,623.79 | $ 65,623.79 | 05/31/20 | |||
20 | 06/16/20 | xxxxxxxxxxxx | $ 47,423.61 | $ 93,047.40 | 06/16/20 | ||||
21 | 06/30/20 | xxxxxxxxxxxx | $ 1.30 | $ 93,048.70 | $ 98,048.70 | 06/30/20 | |||
22 | 07/31/20 | xxxxxxxxxxxx | $ 0.99 | $ 93,049.69 | $ 93,049.69 | 07/31/20 | |||
23 | 08/20/20 | 1507 | xxxxxxxxxxxx | $ (2,800.00) | $ 90,249.69 | 08/25/20 | |||
24 | 08/31/20 | xxxxxxxxxxxx | $ 0.78 | $ 90,250.47 | $ 90,250.47 | 08/31/20 | |||
25 | 09/10/20 | 1508 | xxxxxxxxxxxx | $ (1,661.85) | $ 88,588.62 | 11/27/20 | |||
26 | 09/30/20 | xxxxxxxxxxxx | $ 0.74 | $ 88,589.36 | $ 90,251.21 | 09/30/20 | |||
27 | 10/31/20 | xxxxxxxxxxxx | $ 0.77 | $ 88,590.13 | $ 90,251.98 | 10/31/20 | |||
28 | 11/30/20 | xxxxxxxxxxxx | $ 0.74 | $ 88,590.87 | $ 88,590.87 | 11/30/20 | |||
29 | 12/31/20 | xxxxxxxxxxxx | $ 0.75 | $ 88,591.62 | $ 88,591.62 | 12/31/20 | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G3,G26:G29,G24,G17:G21 | G2 | =+A2 |
E3:E29 | E3 | =+E2+D3 |
Test File.xlsx | |||||||
---|---|---|---|---|---|---|---|
I | J | K | L | M | |||
33 | Book Date | Ck # | Description | Amount | Bank Date | ||
34 | 03/31/20 | 1498 | xxxxxxxxxxxx | $ (10,459.00) | 05/11/20 | ||
35 | 03/31/20 | 1499 | xxxxxxxxxxxx | $ (5,000.00) | 04/28/20 | ||
36 | 03/31/20 | 1500 | xxxxxxxxxxxx | $ (10,000.00) | 05/22/20 | ||
37 | 03/31/20 | 1501 | xxxxxxxxxxxx | $ (5,000.00) | 05/21/20 | ||
38 | 03/31/20 | 1502 | xxxxxxxxxxxx | $ (5,000.00) | 06/12/20 | ||
39 | 03/31/20 | 1503 | xxxxxxxxxxxx | $ (5,000.00) | 06/12/20 | ||
40 | 03/31/20 | 1504 | xxxxxxxxxxxx | $ (5,000.00) | 07/31/20 | ||
41 | 03/31/20 | 1505 | xxxxxxxxxxxx | $ (5,000.00) | 06/12/20 | ||
Sheet1 |