filter dynamic data without filter function

Tdw1990

New Member
Joined
Jul 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,
I'm needing help to find a way to filter data without using the updated filter and sortby functions released in excel 365. Is it possible to filter data using multiple criteria with basic excel functions? I've attached a sheet with cell A4 using the filter function to produce the desired results.
Book1.xlsx
ABCDEFGHIJKLMNOPQR
1 Property Unit(s) Lease Lease Type Area Lease From Lease To Term Monthly Monthly Annual Annual Annual Annual Security LOC Amount/
2 Rent Rent Rent Rent Rec. Misc Deposit Bank Guarantee
3 Per Area Per Area Per Area Per Area
4100A, 100BBen T. Morris10000 - Laureate RE Investors, LP,Houston
5100DFundamental Advisors, Inc.Current Leases
6100EShari Fish Wellness, LLC10000New Cingular Wireless PCS, LLCOffice Net0.006/1/20115/31/2026180.004,087.150.0049,045.800.000.000.000.000.00
7100FActive Campus LLC10000SprintCom, Inc. (Sprint PCS)Office Net0.008/1/20147/31/2034240.004,838.400.0058,060.800.000.000.000.000.00
8100GNRI Energy Partners LLC10000T-Mobile USA, Inc.Office Net0.008/5/20158/4/2030180.003,091.010.0037,092.120.000.000.000.000.00
9100HPeter Busher10000GTE Mobilnet of South Texas LPOffice Net0.009/1/20188/31/202360.004,243.600.0050,923.200.000.000.000.000.00
10100J, 100KMarian Louise Designs LLC10000Tuan TranOffice Net0.001/1/20160.001,500.000.0018,000.000.000.000.000.000.00
11100NTexas Investors Title, LLC10000David RogersOffice Net0.007/1/20190.000.000.000.000.000.000.000.000.00
12100PT.O.S. Advisors, LLC10000100A, 100BBen T. MorrisOffice Net835.0011/1/201810/31/202136.003,677.074.4044,124.8452.840.001.946,500.000.00
13260J. Ron Young10000100DFundamental Advisors, Inc.Office Net322.007/1/20196/30/202236.001,545.004.8018,540.0057.580.003.031,500.000.00
14300Fisher, Johnson & Huguenard, LLP10000100EShari Fish Wellness, LLCOffice Net323.007/1/20191/31/202231.001,050.633.2512,607.5639.030.002.011,000.000.00
15310Three Mile Creek, LLC10000100FActive Campus LLCOffice Net502.005/1/202110/31/20216.002,450.004.8829,400.0058.570.003.880.000.00
16330eCorp Texas, LLC10000100GNRI Energy Partners LLCOffice Net413.005/1/202111/30/20217.001,750.004.2421,000.0050.850.003.151,750.000.00
17450Giammalva Properties10000100HPeter BusherOffice Net256.0011/1/201810/31/202136.00848.563.3110,182.7239.780.002.542,500.000.00
18500Rio Grande E&P, LLC10000100J, 100KMarian Louise Designs LLCOffice Net614.006/1/202111/30/20216.002,700.004.4032,400.0052.770.004.932,700.000.00
19550JVL Advisors, LLC10000100NTexas Investors Title, LLCOffice Net382.007/1/20197/31/202237.001,250.003.2715,000.0039.270.001.701,250.000.00
20660SLM Discovery Ventures, Inc.10000100PT.O.S. Advisors, LLCOffice Net382.0011/1/201810/31/202136.001,131.412.9613,576.9235.540.001.702,000.000.00
21740Crosstimbers Capital Group, LLC10000170The Retail Connection - Houston, LLCOffice Net4,408.0011/1/201810/31/202360.007,714.001.7592,568.0021.0018.071.470.000.00
22770Quail Creek Oil Corporation10000200Elite Insurance Agency, Inc.Office Net5,916.002/1/201910/31/202357.008,972.601.52107,671.2018.2018.072.3166,885.280.00
23790NRI Energy Partners, LLC10000260J. Ron YoungOffice Net4,629.003/1/20172/28/202260.009,065.131.96108,781.5623.5018.071.330.000.00
24100CVACANT10000300Fisher, Johnson & Huguenard, LLPOffice Net5,076.001/1/20183/31/202251.009,801.461.93117,617.5223.1718.071.730.000.00
25100LVACANT10000310Three Mile Creek, LLCOffice Net912.004/1/20185/31/202250.001,786.001.9621,432.0023.5018.071.4232,000.000.00
26100MVACANT10000320GT International, Inc.Office Net2,717.007/1/20187/31/202361.005,320.791.9663,849.4823.5018.072.510.000.00
27105-BSRVACANT10000330eCorp Texas, LLCOffice Net3,274.0010/6/201810/31/202137.006,411.581.9676,938.9623.5017.893.170.000.00
28160-BTRVACANT10000420, 480Raintree Resorts International, Inc.Office Net6,335.001/1/201712/31/2026120.0014,306.542.26171,678.4827.1017.892.720.000.00
29165-BUSVACANT10000440Herbert Scott GallowayOffice Net1,112.002/1/20192/28/202349.002,455.672.2129,468.0426.5018.071.178,087.940.00
30175-TSRVACANT10000450Giammalva PropertiesOffice Net2,259.0010/1/20179/30/202260.004,910.422.1758,925.0426.0818.071.730.000.00
31180-CRFVACANT10000460ESS1B HOLDINGS COMPANY LLCOffice Net2,906.0010/8/20181/7/202463.006,296.332.1775,555.9626.0018.071.4512,228.270.00
32250VACANT10000500Rio Grande E&P, LLCOffice Net6,487.006/1/20188/31/202251.0013,514.582.08162,174.9625.0018.071.8022,423.400.00
33400VACANT10000510Ralph S. O'ConnorOffice Net3,138.008/1/20157/31/202396.009,414.003.00112,968.0036.009.010.930.000.00
34410VACANT10000540National Cardiovascular Partners, LPOffice Net7,110.0012/1/20171/30/202361.0014,516.252.04174,195.0024.5018.070.490.000.00
35555VACANT10000550JVL Advisors, LLCOffice Net2,518.006/1/202011/30/202230.006,504.832.5878,057.9631.0017.891.680.000.00
36575VACANT10000600, 610, 620Tex-Isle Supply, Inc.Office Net10,633.0010/1/20162/28/202488.0029,831.382.81357,976.5633.677.491.250.000.00
37760VACANT10000650Westwood Holdings Group, Inc.Office Net7,772.001/1/20176/30/202490.0016,839.332.17202,071.9626.0018.073.180.000.00
38920BVACANT10000660SLM Discovery Ventures, Inc.Office Net3,218.005/1/20174/30/202260.006,570.082.0478,840.9624.5018.070.910.000.00
3910000700Jones Companies, Inc.Office Net3,863.009/1/20199/30/202685.009,818.462.54117,821.5230.5018.071.850.000.00
4010000740Crosstimbers Capital Group, LLCOffice Net6,230.008/1/201712/31/202265.0012,979.172.08155,750.0425.0018.071.200.000.00
4110000750Vickery & ShepherdOffice Net2,037.007/1/20146/30/2024120.005,092.502.5061,110.0030.0017.892.230.000.00
4210000770Quail Creek Oil CorporationOffice Net3,197.0010/1/20169/30/202160.0011,056.293.46132,675.4841.501.331.830.000.00
4310000785IMI MARINE OPERATIONS, INC.Office Net809.005/1/20194/30/202460.002,056.212.5424,674.5230.5018.072.810.000.00
4410000790NRI Energy Partners, LLCOffice Net2,351.005/1/20194/30/202236.005,877.502.5070,530.0030.0018.071.380.000.00
4510000800Employee Benefit Systems, Inc.Office Net4,361.006/1/20166/30/202497.008,903.712.04106,844.5224.5017.892.230.000.00
4610000888Jones & GrangerOffice Net13,947.006/1/20166/30/202497.0028,475.132.04341,701.5624.5017.892.420.000.00
4710000900The George and Barbara Bush FoundationOffice Net3,553.005/1/20194/30/202460.008,142.292.2997,707.4827.5018.071.010.000.00
4810000920Chase Untermeyer & Gold Sky Energy Corp.Office Net1,651.005/1/20194/30/202460.003,783.542.2945,402.4827.5018.070.920.000.00
4910000950Marble Capital, LPOffice Net5,907.001/1/202012/31/2029120.0015,013.632.54180,163.5630.5017.661.100.000.00
5010000100CVACANT323.000.000.000.000.000.000.000.000.000.00
5110000100LVACANT495.000.000.000.000.000.000.000.000.000.00
5210000100MVACANT483.000.000.000.000.000.000.000.000.000.00
5310000105-BSRVACANT156.000.000.000.000.000.000.000.000.000.00
5410000160-BTRVACANT1,278.000.000.000.000.000.000.000.000.000.00
5510000165-BUSVACANT65.000.000.000.000.000.000.000.000.000.00
5610000175-TSRVACANT137.000.000.000.000.000.000.000.000.000.00
5710000180-CRFVACANT518.000.000.000.000.000.000.000.000.000.00
5810000250VACANT1,886.000.000.000.000.000.000.000.000.000.00
5910000400VACANT6,059.000.000.000.000.000.000.000.000.000.00
6010000410VACANT2,198.000.000.000.000.000.000.000.000.000.00
6110000555VACANT0.000.000.000.000.000.000.000.000.000.00
6210000575VACANT1,766.000.000.000.000.000.000.000.000.000.00
6310000760VACANT2,720.000.000.000.000.000.000.000.000.000.00
6410000920BVACANT0.000.000.000.000.000.000.000.000.000.00
65Total Current150,439.00319,592.232.12########25.4914.071.61160,824.890.00
66
67Total UnitsTotal AreaPercentageMonthly RentAnnual Rent
68Occupied43.00132,355.0087.98319,592.233,835,106.76
69Vacant15.0018,084.0012.020.000.00
70Total58.00150,439.00319,592.23########
71
7210343 - SH Crossing I LP,Houston
73Current Leases
7410343110Strayer University, LLCOffice Net7,519.0012/1/20175/31/202590.0010,338.631.38124,063.5616.509.880.000.000.00
7510343200Brock Enterprises, Inc.Office Net27,860.0011/1/20084/30/2026210.0038,307.501.38459,690.0016.509.881.040.000.00
7610343220Carrier CorporationOffice Net4,878.0010/1/201812/31/202363.006,300.751.2975,609.0015.509.880.0010,382.010.00
7710343225Ziebel US, Inc.Office Net4,993.006/1/20185/31/202360.007,281.461.4687,377.5217.509.880.0011,458.940.00
7810343230Iberdrola Solutions LLCOffice Net3,517.002/15/20206/30/202565.004,835.881.3858,030.5616.509.880.008,270.810.00
7910343310Ricoh USA, Inc.Office Net15,121.0012/15/20184/14/202464.0022,051.461.46264,617.5217.509.880.000.000.00
8010343325Aqua Terra Water Management, LPOffice Net7,355.003/1/20195/31/202351.0010,113.131.38121,357.5616.509.880.0016,266.810.00
8110343100VACANT17,750.000.000.000.000.000.000.000.000.000.00
8210343120VACANT13,998.000.000.000.000.000.000.000.000.000.00
8310343150VACANT11,271.000.000.000.000.000.000.000.000.000.00
8410343210VACANT7,248.000.000.000.000.000.000.000.000.000.00
8510343215VACANT5,386.000.000.000.000.000.000.000.000.000.00
8610343300VACANT27,740.000.000.000.000.000.000.000.000.000.00
8710343350VACANT4,539.000.000.000.000.000.000.000.000.000.00
88Total Current159,175.0099,228.810.62########7.484.420.1846,378.570.00
Sheet1
Cell Formulas
RangeFormula
A4:B38A4=FILTER($D$5:$E$200,(C5:C200="10000")*((E5:E200="VACANT")+(YEAR(I5:I200)=2022)+(YEAR(I5:I200)=2022-1)),"")
Dynamic array formulas.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try:

Book1
ABCDEFGHIJKLMNOPQR
1 Property Unit(s) Lease Lease Type Area Lease From Lease To Term Monthly Monthly Annual Annual Annual Annual Security LOC Amount/
2 Rent Rent Rent Rent Rec. Misc Deposit Bank Guarantee
3 Per Area Per Area Per Area Per Area
4100A, 100BBen T. Morris10000 - Laureate RE Investors, LP,Houston
5100DFundamental Advisors, Inc.Current Leases
6100EShari Fish Wellness, LLC10000New Cingular Wireless PCS, LLCOffice Net06/1/20115/31/20261804087.15049045.800000
7100FActive Campus LLC10000SprintCom, Inc. (Sprint PCS)Office Net08/1/20147/31/20342404838.4058060.800000
8100GNRI Energy Partners LLC10000T-Mobile USA, Inc.Office Net08/5/20158/4/20301803091.01037092.1200000
9100HPeter Busher10000GTE Mobilnet of South Texas LPOffice Net09/1/20188/31/2023604243.6050923.200000
10100J, 100KMarian Louise Designs LLC10000Tuan TranOffice Net01/1/20160150001800000000
11100NTexas Investors Title, LLC10000David RogersOffice Net07/1/2019000000000
12100PT.O.S. Advisors, LLC10000100A, 100BBen T. MorrisOffice Net83511/1/201810/31/2021363677.074.444124.8452.8401.9465000
13260J. Ron Young10000100DFundamental Advisors, Inc.Office Net3227/1/20196/30/20223615454.81854057.5803.0315000
14300Fisher, Johnson & Huguenard, LLP10000100EShari Fish Wellness, LLCOffice Net3237/1/20191/31/2022311050.633.2512607.5639.0302.0110000
15310Three Mile Creek, LLC10000100FActive Campus LLCOffice Net5025/1/202110/31/2021624504.882940058.5703.8800
16330eCorp Texas, LLC10000100GNRI Energy Partners LLCOffice Net4135/1/202111/30/2021717504.242100050.8503.1517500
17450Giammalva Properties10000100HPeter BusherOffice Net25611/1/201810/31/202136848.563.3110182.7239.7802.5425000
18500Rio Grande E&P, LLC10000100J, 100KMarian Louise Designs LLCOffice Net6146/1/202111/30/2021627004.43240052.7704.9327000
19550JVL Advisors, LLC10000100NTexas Investors Title, LLCOffice Net3827/1/20197/31/20223712503.271500039.2701.712500
20660SLM Discovery Ventures, Inc.10000100PT.O.S. Advisors, LLCOffice Net38211/1/201810/31/2021361131.412.9613576.9235.5401.720000
21740Crosstimbers Capital Group, LLC10000170The Retail Connection - Houston, LLCOffice Net440811/1/201810/31/20236077141.75925682118.071.4700
22770Quail Creek Oil Corporation10000200Elite Insurance Agency, Inc.Office Net59162/1/201910/31/2023578972.61.52107671.218.218.072.3166885.280
23790NRI Energy Partners, LLC10000260J. Ron YoungOffice Net46293/1/20172/28/2022609065.131.96108781.623.518.071.3300
24100CVACANT10000300Fisher, Johnson & Huguenard, LLPOffice Net50761/1/20183/31/2022519801.461.93117617.523.1718.071.7300
25100LVACANT10000310Three Mile Creek, LLCOffice Net9124/1/20185/31/20225017861.962143223.518.071.42320000
26100MVACANT10000320GT International, Inc.Office Net27177/1/20187/31/2023615320.791.9663849.4823.518.072.5100
27105-BSRVACANT10000330eCorp Texas, LLCOffice Net327410/6/201810/31/2021376411.581.9676938.9623.517.893.1700
28160-BTRVACANT10000420, 480Raintree Resorts International, Inc.Office Net63351/1/201712/31/202612014306.542.26171678.527.117.892.7200
29165-BUSVACANT10000440Herbert Scott GallowayOffice Net11122/1/20192/28/2023492455.672.2129468.0426.518.071.178087.940
30175-TSRVACANT10000450Giammalva PropertiesOffice Net225910/1/20179/30/2022604910.422.1758925.0426.0818.071.7300
31180-CRFVACANT10000460ESS1B HOLDINGS COMPANY LLCOffice Net290610/8/20181/7/2024636296.332.1775555.962618.071.4512228.270
32250VACANT10000500Rio Grande E&P, LLCOffice Net64876/1/20188/31/20225113514.582.081621752518.071.822423.40
33400VACANT10000510Ralph S. O'ConnorOffice Net31388/1/20157/31/20239694143112968369.010.9300
34410VACANT10000540National Cardiovascular Partners, LPOffice Net711012/1/20171/30/20236114516.252.0417419524.518.070.4900
35555VACANT10000550JVL Advisors, LLCOffice Net25186/1/202011/30/2022306504.832.5878057.963117.891.6800
36575VACANT10000600, 610, 620Tex-Isle Supply, Inc.Office Net1063310/1/20162/28/20248829831.382.81357976.633.677.491.2500
37760VACANT10000650Westwood Holdings Group, Inc.Office Net77721/1/20176/30/20249016839.332.172020722618.073.1800
38920BVACANT10000660SLM Discovery Ventures, Inc.Office Net32185/1/20174/30/2022606570.082.0478840.9624.518.070.9100
39  10000700Jones Companies, Inc.Office Net38639/1/20199/30/2026859818.462.54117821.530.518.071.8500
4010000740Crosstimbers Capital Group, LLCOffice Net62308/1/201712/31/20226512979.172.081557502518.071.200
4110000750Vickery & ShepherdOffice Net20377/1/20146/30/20241205092.52.5611103017.892.2300
4210000770Quail Creek Oil CorporationOffice Net319710/1/20169/30/20216011056.293.46132675.541.51.331.8300
4310000785IMI MARINE OPERATIONS, INC.Office Net8095/1/20194/30/2024602056.212.5424674.5230.518.072.8100
4410000790NRI Energy Partners, LLCOffice Net23515/1/20194/30/2022365877.52.5705303018.071.3800
4510000800Employee Benefit Systems, Inc.Office Net43616/1/20166/30/2024978903.712.04106844.524.517.892.2300
4610000888Jones & GrangerOffice Net139476/1/20166/30/20249728475.132.04341701.624.517.892.4200
4710000900The George and Barbara Bush FoundationOffice Net35535/1/20194/30/2024608142.292.2997707.4827.518.071.0100
4810000920Chase Untermeyer & Gold Sky Energy Corp.Office Net16515/1/20194/30/2024603783.542.2945402.4827.518.070.9200
4910000950Marble Capital, LPOffice Net59071/1/202012/31/202912015013.632.54180163.630.517.661.100
5010000100CVACANT323000000000
5110000100LVACANT495000000000
5210000100MVACANT483000000000
5310000105-BSRVACANT156000000000
5410000160-BTRVACANT1278000000000
5510000165-BUSVACANT65000000000
5610000175-TSRVACANT137000000000
5710000180-CRFVACANT518000000000
5810000250VACANT1886000000000
5910000400VACANT6059000000000
6010000410VACANT2198000000000
6110000555VACANT0000000000
6210000575VACANT1766000000000
6310000760VACANT2720000000000
6410000920BVACANT0000000000
65Total Current150439319592.22.12383510725.4914.071.61160824.90
66
67Total UnitsTotal AreaPercentageMonthly RentAnnual Rent
68Occupied4313235587.97918319592.23835107
69Vacant151808412.0208200
70Total58150439319592.23835107
71
7210343 - SH Crossing I LP,Houston
73Current Leases
7410343110Strayer University, LLCOffice Net751912/1/20175/31/20259010338.631.38124063.616.59.88000
7510343200Brock Enterprises, Inc.Office Net2786011/1/20084/30/202621038307.51.3845969016.59.881.0400
7610343220Carrier CorporationOffice Net487810/1/201812/31/2023636300.751.297560915.59.88010382.010
7710343225Ziebel US, Inc.Office Net49936/1/20185/31/2023607281.461.4687377.5217.59.88011458.940
7810343230Iberdrola Solutions LLCOffice Net35172/15/20206/30/2025654835.881.3858030.5616.59.8808270.810
7910343310Ricoh USA, Inc.Office Net15121#########4/14/20246422051.461.46264617.517.59.88000
8010343325Aqua Terra Water Management, LPOffice Net73553/1/20195/31/20235110113.131.38121357.616.59.88016266.810
8110343100VACANT17750000000000
8210343120VACANT13998000000000
8310343150VACANT11271000000000
8410343210VACANT7248000000000
8510343215VACANT5386000000000
8610343300VACANT27740000000000
8710343350VACANT4539000000000
88Total Current15917599228.810.6211907467.484.420.1846378.570
Sheet21
Cell Formulas
RangeFormula
A4:B39A4=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$6:D$200)/($C$6:$C$200=10000)/SIGN(($E$6:$E$200="VACANT")+(YEAR($I$6:$I$200)=2022)+(YEAR($I$6:$I$200)=2022-1)),ROWS(A$4:A4))),"")


Drag the formulas down as needed. I had to change the "10000" to 10000 because of how the copy/paste worked.
 
Upvote 0

Forum statistics

Threads
1,223,961
Messages
6,175,651
Members
452,664
Latest member
alpserbetli

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