Best way to handle copy down of formulas adjacent to filter arrays in excel

Question

edited to add an example table

I use excel's filter and unique functions to retrieve arrays from a source table. The first array is typical set of dates, followed by data. Next to the retrieved arrays, I have columns with formulas.

Once the source table grows, filter function is always up to date, adding new rows in the end...but the columns with formulas do not. You need to copy down the formulas. Also, you cannot make a table of a range if the columns have spill functions like filter or unique.

What would be the recommended way to handle this? Is there a better way than making a macro that copies down the formulas?

As an example, the source table has a growing number of dates and some categories with values:

date	category	value
1.1.2022	A	1.2
1.1.2022	A	0.5
1.1.2022	B	0.2
1.1.2022	B	2.2
2.1.2022	A	0.1
2.1.2022	A	0.3
2.1.2022	B	1.2

...

Now in the summary table, I use unique function to retrieve the dates in the first column. This spills down automatically - so far so good. In the second column (category A), I use sum(filter(..)) function to sum all values in the source table where category = A and date = the date on the same row in the first column:

unique date	cat A	cat B
1.1.2022	1.7	2.4
2.1.2022	0.4	1.2

This is problematic since filter formula looks like this (assuming the above table starts from cell A1):

=sum(filter(source[value],(source[category]=B$1)*isnumber(match(source[date],$A2))))

Hashtag did not seem to work in the last parameter ($A2), e.g. replacing $A2 by offset($A2#,0,0,1) worked only on the first row.

Answer 1

I often use OFFSET in combination with the spill-range syntax that @Rory cited: For example, if your FILTER/UNIQUE formula in cell A2 spills to columns A and B, use OFFSET(A2#,0,1,,1) in a function that acts on the values in column B only. The result will be a spill range for each row of your original spill range.

Of course, you can also offset rows that way too (e.g., to calculate incremental changes between rows: =OFFSET(A2#,0,1,,1)-OFFSET(A2#,-1,1,,1) for the prior row, so long as the value in B1 does not cause an error).

Additionally, Office365 versions have BYROW and BYCOL that you can use to act on each row or column of a spill range. For example, to find the max value of each row, =BYROW(A2#,LAMBDA(r, MAX(r))).

Answer 2

Thanks @pdtcaskey I had a similar challenge and your mention of BYROW and LAMBDA did the trick for me!

For above scenario, let's assume the source table is an Excel Table with the elusive name Source and columns Date, Category, Value, situated at A1.

For the summary, assuming it's in a separate sheet and also starts at A1, you can do this: Put the headers in row 1

In A2 put: =SORT(UNIQUE(Source[Date]))

In B2 put: =BYROW(A2#;LAMBDA(r;SUM(FILTER(Source[Value]; (Source[Category]="A")*(Source[Date]=r)))))

In C2 put: =BYROW(A2#;LAMBDA(r;SUM(FILTER(Source[Value];(Source[Category]="B")*(Source[Date]=r)))))