Chapter 17 · 17.5
Lists that update themselves with FILTER, UNIQUE and SORT
A customer list without duplicates, an overview of all open orders, a product list ranked by revenue: you used to build lists like these by copying, filtering and sorting, and then do it all again whenever something changed. With the FILTER, UNIQUE and SORT functions, you write one formula that builds the whole list and updates it automatically. Euro-Office supports these so-called dynamic arrays: the result "spills" over as many cells as it needs.
As an example, we use the Bestellingen worksheet of Bakkerij De Korenaar: column A holds the date, B the customer, C the product, D the amount and E whether the order has been paid (ja or nee).
A unique, sorted customer list
Type =SORT(UNIQUE(FILTER(B2:B500,B2:B500<>""))) in G2 and press Enter. That's all: you get each customer once, in alphabetical order, listed from G2 downwards.
- FILTER(B2:B500,B2:B500<>"") leaves out the empty rows. This lets you choose a generous range for orders that will be added later.
- UNIQUE removes the duplicates.
- SORT puts them in order.

When an order from a new customer is added, for example Tandarts Bos in row 14, that customer immediately appears in the right place in the list. To change the list, edit the formula in the first cell, G2.
All open orders for this month
FILTER can also return entire rows, with several conditions:
=FILTER(A2:D500,(E2:E500="nee")*(A2:A500>=EOMONTH(TODAY(),-1)+1),"nothing open")
- The first part is what you want to see: columns A to D.
- The second part contains the conditions. Each condition goes in brackets. A * between them means and, a + means or.
- EOMONTH(TODAY(),-1)+1 is the first day of this month.
- The third part is the text to show if there are no results.

Please note: Euro-Office applies the format of the first column to the whole result. If the result starts with a date, the amounts in the last column are shown as dates too: 36.00 becomes 05/02/1900. Select that column of the result and choose the Currency or Number format yourself (see Formatting numbers, dates and currency).
Sorting and choosing columns
- By amount, highest first: =SORT(FILTER(A2:D500,E2:E500="nee"),4,-1). The 4 is the fourth column of the result, -1 means descending.
- Sorting by a column you don't show: =SORTBY(FILTER(B2:B500,E2:E500="nee"),FILTER(D2:D500,E2:E500="nee"),-1) returns only the customer names, sorted by amount.
- Only customer and amount: =CHOOSECOLS(FILTER(A2:D500,E2:E500="nee"),2,4).
Something is in the way
If a cell where the result needs to go already contains something, the list can't spill. In Excel you then see the #SPILL! error. Euro-Office leaves the cell with the formula empty instead; click it and you'll see a dotted line around the area the result needs. Clear the cells in that area and the list appears straight away. So keep some space free below and next to a formula like this.

Calculating with whole columns in one formula
Some formulas calculate with an entire column at once, such as =SUM(D2:D13*(E2:E13="nee")): the total amount of all orders that haven't been paid yet, here 258.50. In Euro-Office, a formula like this works with a simple Enter. However, formulas that use IF over a whole column inside another function, such as =LARGE(IF(B2:B37="Pie",C2:C37),1), aren't calculated correctly with a simple Enter: you get #NUM! or a wrong number. Finish a formula like this with Ctrl+Shift+Enter. The formula then gets curly brackets {…} and returns the correct result. Often you can do without IF and use FILTER instead: =LARGE(FILTER(C2:C37,B2:B37="Pie"),1).
What isn't possible
- LET and LAMBDA, and the functions built on them such as BYROW, MAP and GROUPBY, aren't supported in Euro-Office. An Excel file using these functions shows #NAME?. Use a helper column instead, or see Excel-formules met LET of LAMBDA: wat je in Euro-Office in plaats daarvan doet for alternatives.
- A colleague using Excel 2019 or older will see #NAME? for these functions.
Pitfalls
- Whole columns such as B:B make the formula slow. Choose a generous, fixed range such as B2:B500.
- No TRUE or FALSE without brackets in these formulas; use 1 and 0, or TRUE() and FALSE() (see Copying formulas from Excel: English function names and the comma).
