Filter both columns and rows using criteria

2

2077delta

Active Member
Joined
Feb 17, 2002
Messages
252
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1Book DateCk #DescriptionAmountBalanceBalanceBank Date
201/01/20xxxxxxxxxxxx$ 46,777.7701/01/20
301/31/20xxxxxxxxxxxx$ 1.19$ 46,778.96$ 46,778.9601/31/20
402/14/201496xxxxxxxxxxxx$ (10.00)$ 46,768.9602/28/20
502/14/201497xxxxxxxxxxxx$ (150.00)$ 46,618.9603/03/20
602/29/20xxxxxxxxxxxx$ 1.11$ 46,620.07$ 46,770.0702/29/20
703/31/201498xxxxxxxxxxxx$ (10,459.00)$ 36,161.0705/11/20
803/31/201499xxxxxxxxxxxx$ (5,000.00)$ 31,161.0704/28/20
903/31/201500xxxxxxxxxxxx$ (10,000.00)$ 21,161.0705/22/20
1003/31/201501xxxxxxxxxxxx$ (5,000.00)$ 16,161.0705/21/20
1103/31/201502xxxxxxxxxxxx$ (5,000.00)$ 11,161.0706/12/20
1203/31/201503xxxxxxxxxxxx$ (5,000.00)$ 6,161.0706/12/20
1303/31/201504xxxxxxxxxxxx$ (5,000.00)$ 1,161.0707/31/20
1403/31/201505xxxxxxxxxxxx$ (5,000.00)$ (3,838.93)06/12/20
1503/31/20xxxxxxxxxxxx$ 0.84$ (3,838.09)$ 46,620.9103/31/20
1604/17/201506xxxxxxxxxxxx$ (1,000.00)$ (4,838.09)04/27/20
1704/02/20xxxxxxxxxxxx$ 50,459.00$ 45,620.9104/02/20
1804/30/20xxxxxxxxxxxx$ 1.54$ 45,622.45$ 91,081.4504/30/20
1905/31/20xxxxxxxxxxxx$ 1.34$ 45,623.79$ 65,623.7905/31/20
2006/16/20xxxxxxxxxxxx$ 47,423.61$ 93,047.4006/16/20
2106/30/20xxxxxxxxxxxx$ 1.30$ 93,048.70$ 98,048.7006/30/20
2207/31/20xxxxxxxxxxxx$ 0.99$ 93,049.69$ 93,049.6907/31/20
2308/20/201507xxxxxxxxxxxx$ (2,800.00)$ 90,249.6908/25/20
2408/31/20xxxxxxxxxxxx$ 0.78$ 90,250.47$ 90,250.4708/31/20
2509/10/201508xxxxxxxxxxxx$ (1,661.85)$ 88,588.6211/27/20
2609/30/20xxxxxxxxxxxx$ 0.74$ 88,589.36$ 90,251.2109/30/20
2710/31/20xxxxxxxxxxxx$ 0.77$ 88,590.13$ 90,251.9810/31/20
2811/30/20xxxxxxxxxxxx$ 0.74$ 88,590.87$ 88,590.8711/30/20
2912/31/20xxxxxxxxxxxx$ 0.75$ 88,591.62$ 88,591.6212/31/20
Sheet1
Cell Formulas
RangeFormula
G2:G3,G26:G29,G24,G17:G21G2=+A2
E3:E29E3=+E2+D3
Test File.xlsx
IJKLM
33Book DateCk #DescriptionAmountBank Date
3403/31/201498xxxxxxxxxxxx$ (10,459.00)05/11/20
3503/31/201499xxxxxxxxxxxx$ (5,000.00)04/28/20
3603/31/201500xxxxxxxxxxxx$ (10,000.00)05/22/20
3703/31/201501xxxxxxxxxxxx$ (5,000.00)05/21/20
3803/31/201502xxxxxxxxxxxx$ (5,000.00)06/12/20
3903/31/201503xxxxxxxxxxxx$ (5,000.00)06/12/20
4003/31/201504xxxxxxxxxxxx$ (5,000.00)07/31/20
4103/31/201505xxxxxxxxxxxx$ (5,000.00)06/12/20
Sheet1
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
You can use the function FILTER to filter the table; for example:
Excel Formula: 
=FILTER(A2:G100,(A2:A100>DATE(2020,3,1))*(D2:D100<0))
This will filter by BookDate>1-mar-2020 AND negative Amount

Then you can use INDEX to extract the columns you want; unfortunately you cannot use an array of constant to extract more than 1 columns (eg {1\3\7}, to extract columns 1, 3, 7). Therefore you need to use several formulas in several columns to extract several filtered columns.
For example
-In N2, use =INDEX(FILTER(A2:G100,(A2:A100>DATE(2020,3,1))*(D2:D100<0)),0,1) This will return column 1 of the filtered array
-in O2, use =INDEX(FILTER(A2:G100,(A2:A100>DATE(2020,3,1))*(D2:D100<0)),0,3) This will return column 3
-in P2, use =INDEX(FILTER(A2:G100,(A2:A100>DATE(2020,3,1))*(D2:D100<0)),0,7) This will return column 7

Bye
 
Upvote 0
Anthony47 said:
unfortunately you cannot use an array of constant to extract more than 1 columns
Click to expand...
Actually, you can - if you detail the rows too.

@2077delta
You obviously have M/Soft 365 but it would help in the future if you made that obvious by updating your Account details (click your user name at the top right of the forum) so helpers always know. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

21 08 12.xlsm
ABCDEFGHIJKLMNO
1Book DateCk #DescriptionAmountBalanceBalanceBank DateBook DateCk #DescriptionAmountBank Date31/03/2020
21/01/2020xxxxxxxxxxxx46777.771/01/202031/03/20201498xxxxxxxxxxxx-1045911/05/2020
331/01/2020xxxxxxxxxxxx1.1946778.9646778.9631/01/202031/03/20201499xxxxxxxxxxxx-500028/04/2020
414/02/20201496xxxxxxxxxxxx-1046768.9628/02/202031/03/20201500xxxxxxxxxxxx-1000022/05/2020
514/02/20201497xxxxxxxxxxxx-15046618.963/03/202031/03/20201501xxxxxxxxxxxx-500021/05/2020
629/02/2020xxxxxxxxxxxx1.1146620.0746770.0729/02/202031/03/20201502xxxxxxxxxxxx-500012/06/2020
731/03/20201498xxxxxxxxxxxx-1045936161.0711/05/202031/03/20201503xxxxxxxxxxxx-500012/06/2020
831/03/20201499xxxxxxxxxxxx-500031161.0728/04/202031/03/20201504xxxxxxxxxxxx-500031/07/2020
931/03/20201500xxxxxxxxxxxx-1000021161.0722/05/202031/03/20201505xxxxxxxxxxxx-500012/06/2020
1031/03/20201501xxxxxxxxxxxx-500016161.0721/05/2020
1131/03/20201502xxxxxxxxxxxx-500011161.0712/06/2020
1231/03/20201503xxxxxxxxxxxx-50006161.0712/06/2020
1331/03/20201504xxxxxxxxxxxx-50001161.0731/07/2020
1431/03/20201505xxxxxxxxxxxx-5000-3838.9312/06/2020
1531/03/2020xxxxxxxxxxxx0.84-3838.0946620.9131/03/2020
1617/04/20201506xxxxxxxxxxxx-1000-4838.0927/04/2020
172/04/2020xxxxxxxxxxxx5045945620.912/04/2020
1830/04/2020xxxxxxxxxxxx1.5445622.4591081.4530/04/2020
1931/05/2020xxxxxxxxxxxx1.3445623.7965623.7931/05/2020
2016/06/2020xxxxxxxxxxxx47423.6193047.416/06/2020
2130/06/2020xxxxxxxxxxxx1.393048.798048.730/06/2020
2231/07/2020xxxxxxxxxxxx0.9993049.6993049.6931/07/2020
2320/08/20201507xxxxxxxxxxxx-280090249.6925/08/2020
2431/08/2020xxxxxxxxxxxx0.7890250.4790250.4731/08/2020
2510/09/20201508xxxxxxxxxxxx-1661.8588588.6227/11/2020
2630/09/2020xxxxxxxxxxxx0.7488589.3690251.2130/09/2020
2731/10/2020xxxxxxxxxxxx0.7788590.1390251.9831/10/2020
2830/11/2020xxxxxxxxxxxx0.7488590.8788590.8730/11/2020
2931/12/2020xxxxxxxxxxxx0.7588591.6288591.6231/12/2020
Extract
Cell Formulas
RangeFormula
I2:M9I2=LET(fltr,FILTER(A2:G29,(A2:A29<=os_date)*(B2:B29>0)*(G2:G29>os_date)),INDEX(fltr,SEQUENCE(ROWS(fltr)),{1,2,3,4,7}))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
os_date=Extract!$O$1I2
 
Upvote 0
Another option
+Fluff 1.xlsm
ABCDEFGHIJKLMNO
1Book DateCk #DescriptionAmountBalanceBalanceBank DateBook DateCk #DescriptionAmountBank Date31/03/2020
201/01/2020xxxxxxxxxxxx46777.7701/01/202031/03/20201498xxxxxxxxxxxx-1045911/05/2020
331/01/2020xxxxxxxxxxxx1.1946778.9646778.9631/01/202031/03/20201499xxxxxxxxxxxx-500028/04/2020
414/02/20201496xxxxxxxxxxxx-1046768.9628/02/202031/03/20201500xxxxxxxxxxxx-1000022/05/2020
514/02/20201497xxxxxxxxxxxx-15046618.9603/03/202031/03/20201501xxxxxxxxxxxx-500021/05/2020
629/02/2020xxxxxxxxxxxx1.1146620.0746770.0729/02/202031/03/20201502xxxxxxxxxxxx-500012/06/2020
731/03/20201498xxxxxxxxxxxx-1045936161.0711/05/202031/03/20201503xxxxxxxxxxxx-500012/06/2020
831/03/20201499xxxxxxxxxxxx-500031161.0728/04/202031/03/20201504xxxxxxxxxxxx-500031/07/2020
931/03/20201500xxxxxxxxxxxx-1000021161.0722/05/202031/03/20201505xxxxxxxxxxxx-500012/06/2020
1031/03/20201501xxxxxxxxxxxx-500016161.0721/05/2020
1131/03/20201502xxxxxxxxxxxx-500011161.0712/06/2020
1231/03/20201503xxxxxxxxxxxx-50006161.0712/06/2020
1331/03/20201504xxxxxxxxxxxx-50001161.0731/07/2020
1431/03/20201505xxxxxxxxxxxx-5000-3838.9312/06/2020
1531/03/2020xxxxxxxxxxxx0.84-3838.0946620.9131/03/2020
1617/04/20201506xxxxxxxxxxxx-1000-4838.0927/04/2020
1702/04/2020xxxxxxxxxxxx5045945620.9102/04/2020
1830/04/2020xxxxxxxxxxxx1.5445622.4591081.4530/04/2020
1931/05/2020xxxxxxxxxxxx1.3445623.7965623.7931/05/2020
2016/06/2020xxxxxxxxxxxx47423.6193047.416/06/2020
2130/06/2020xxxxxxxxxxxx1.393048.798048.730/06/2020
2231/07/2020xxxxxxxxxxxx0.9993049.6993049.6931/07/2020
2320/08/20201507xxxxxxxxxxxx-280090249.6925/08/2020
2431/08/2020xxxxxxxxxxxx0.7890250.4790250.4731/08/2020
2510/09/20201508xxxxxxxxxxxx-1661.8588588.6227/11/2020
2630/09/2020xxxxxxxxxxxx0.7488589.3690251.2130/09/2020
2731/10/2020xxxxxxxxxxxx0.7788590.1390251.9831/10/2020
2830/11/2020xxxxxxxxxxxx0.7488590.8788590.8730/11/2020
2931/12/2020xxxxxxxxxxxx0.7588591.6288591.6231/12/2020
List
Cell Formulas
RangeFormula
I2:M9I2=FILTER(FILTER(A2:G29,(A2:A29<=os_date)*(B2:B29>0)*(G2:G29>os_date)),COUNTIF(I1:M1,A1:G1))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
os_date=List!$O$1I2
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Need Help in Dates
2 3
Replies
27
Views
931
Dave Patton
D
M
  • Question
How to filter/lookup rows of data based on the latest date in a cell
Replies
8
Views
892
Peter_SSs
Peter_SSs
J
  • Solved
How to aggregate data based on multiple criteria?
Replies
8
Views
537
Jurez
J
S
  • Question
Date column not working
Replies
4
Views
482
awoohaw
awoohaw
O
  • Solved
need macro to deal with 8000 rows to merge items based on helper column
2
Replies
11
Views
594
Akuini
Akuini

Forum statistics

Threads
1,214,922
Messages
6,122,281
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top