Forum Discussion
kabeerkhann
Sep 13, 2023Copper Contributor
2013 doesn't have =filter() function. ANY alternate function?
I have Microsoft excel 365 in that filter function is available and i create a follow up sheet linked with database sheet where in database user will store data from the form. In database there is a column Follow up date.
I want to show the all the data which will meet a date condition. I have done by using filter function but in 2013 it doesn't have filter function what function can i use to perform the same operation?
- ecovonreinIron Contributor
There is a way to abuse SUMPRODUCT to produce a SQL statement. There is a way to arrange an Excel sheet like an SQL table. My database has plants (across rows by name, each plant occupying multiple rows labelled by attributes like "Production") and associated data (across columns by Dates). When all data be called "DBase" and the column headers "Dates", then your can filter (query) the production data as follows:
1 =SUMPRODUCT((Plants="Apollo")*(Attribute="Production")*ROW(Plants))
to obtain the row number of the total production data for Apollo and
2 =SUMPRODUCT((31-12-2021<Dates)*(Dates<=31-12-2022)*INDEX(Dbase,rowAbove,0))
Hope this makes sense. You'll need a complicated formula - see for example Extract all rows from a range that meet criteria in one column
- OliverScheurichGold Contributor
=IFERROR(INDEX($A$2:$A$23,SMALL(IF($B$2:$B$23>60,ROW($B$2:$B$23)-1),ROW(A1))),"")
An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula filters the values in column A if the corresponding value in column B is greater than 60. The screenshot is taken from Excel 2013.