FILTER function

Kirstine
Kirstine ✭✭✭✭✭✭

Is there an equivalent of the FILTER function in Excel?

=FILTER (array, include, [if_empty])

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. We will start by adding 3 columns to your "Source Sheet".

    Auto:

    Auto-number column with no special formatting.


    Row:

    Text/number column with the following column formula...

    =MATCH(Auto@row, Auto:Auto, 0)


    Count:

    Text/number column with the following column formula...

    =IF(Type@row = "Site Pack", COUNTIFS(Row:Row, @cell <= Row@row, Type:Type, "Site Pack"))


    Now we move on to the "Alert Sheet". There are going to be a few things that will initially seem redundant, but it ensures everything will continue to work even if the "Source Sheet" gets rearranged, sorted, etc.

    [Primary Column]:

    Enter some kind of data in every row until you have pre-filled as many rows as you anticipate needed (and some extra rows just in case). I used 1 in row 1, 2 in row 2, 3 in row 3, so on and so forth, but you could literally enter anything including the same exact thing on all rows. We just need the rows pre-filled.


    Auto:

    Same as Source Sheet


    Row:

    Same as Source Sheet


    RefNum:

    Text/number column with the following column formula...

    =IFERROR(INDEX({Source Sheet Our ref Column}, MATCH(Row@row, {Source Sheet Count Column}, 0)), "")


    Invoices:

    Text/number column with the following column formula...

    =IF(RefNum@row <> "", JOIN(COLLECT({Source Sheet Invoice Number Column}, {Source Sheet Invoice Number Column}, @cell <> "", {Source Sheet Our ref Column}, RefNum@row), ", "))


    Date Received:

    Date column with the following column formula...

    =IFERROR(INDEX({Source Sheet Received Date Column}, MATCH(Row@row, {Source Sheet Count Column}, 0)), "")


    Now you can build your alerts on this sheet. If there is a concern that you may run out of rows, you can add a text/number column and use the following in any cell.

    =COUNT(RefNum:RefNum)


    This will tell you how many rows are being used. You can then set up an additional alert to send when that number gets within a certain amount of however many rows you pre-filled. So if you pre-filled 300 rows, you can set up an alert to send whenever this count is greater than 275 so that you know you may want to go in and add rows by entering more data in the [Primary Column]. Everything else being column formulas means that is the only step you should have to take to add rows.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @LEllis Using both columns ensures that the number 1 STAYS on row 1 if you decide to sort. The numbers in the Auto-Number column are locked to that row, so after sorting, inserting new rows, deleting rows those numbers could be all kinds of jumbled up.


    If you are never sorting, never deleting rows, and always adding new rows at the bottom of the sheet, then you could use just the Auto-Number column.

«134

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!