FILTER function

Kirstine

Is there an equivalent of the FILTER function in Excel?

=FILTER (array, include, [if_empty])

Paul Newcome

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

@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.

Paul Newcome

Not exactly, but I am sure we could figure something else out as a solution. Are you able to provide some screenshots with manually entered mock data that shows exactly what you are trying to accomplish with your particular layout?

Kirstine

Hi @Paul Newcome, thanks for your response! I'm not even sure if that would be the right function anyway, so your help would be very much appreciated.

Basically, we receive invoices for jobs, but don't pay them until we've got the 'Site Pack' back. I've set a couple of sheets up to copy rows into the sheet I'm working on, so that we have details of every invoice and Site Pack we've received and approved (only ever one Site Pack per job).

What I'm looking to do is send an Automated email to accounts once we receive the site Pack for a job (each job represented by unique ref in [Our ref] column) which will mean they can pay the invoice.

Hope that makes sense?

Paul Newcome

Are you able to just set up the automation to trigger when the [Received Date] is not blank (changes to any value) and [Type} is "Site Pack"?

Kirstine

I'd really like it to reference the specific invoices. 😕

Paul Newcome

I'm not sure I follow. Each Site Pack can have multiple invoices?

Kirstine

Sorry! Yes, there's likely to be at least two invoices to ever Site Pack/job.

Paul Newcome

Ok. SO basically you want an automation that sends when the date received on row 9 is populated, and you want that automation to list out invoice numbers for rows 2 - 8?

Kirstine

Yes! Although they probably won't all be in a nice neat order like that...!

Paul Newcome

Ok. To do this we are going to need to get a little creative and do some planning ahead.

How many different Site Packs do you anticipate having in your sheet at any one given time?

Kirstine

Ha ha! I love getting creative! 🤣

Last year we had just under 200.

Paul Newcome

Ok. So lets go ahead and assume that we are going to be pulling for 300 rows. The formulas will be the same, but you will want to pre-fill a sheet.

Let me put something together today so I can make sure that the formulas are correct. I'll let you know as soon as I have something for you.

Kirstine

Paul Newcome

SO I want to make sure I have this right...

You want to take this:

And then make it look like this:

So that when the [Date Received] column in the second screenshot is populated an alert will be sent that contains the Invoices column data for that row?

Kirstine

Ooo, yes! That would work!

Paul Newcome

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.