Use TEXTJOIN with FILTER and reference to a spill range

Question

I am trying to query a list of sales stored in Excel by using TEXTJOIN and FILTER and referencing a spill range and this does not work.

Here is the context:

• I have a table tabSales containing the sales, with the following data : date, customer, amount, year (calculated using =YEAR([Date])

• I want to display on a report sheet for every year the list of customers who were sold something during the year as a merged text (e.g. 2020 ⇨ "John Smith, Alan Dulles")

Here is what I have tried on the report sheet:

• I have used the SORT and UNIQUE functions to make the report display on column D the list of years where some sales occurred : =SORT(UNIQUE(tabSales[Year]), , -1) (formula input in cell A2)

• I have then tried to use the JOINTEXT and FILTER functions and Excel's spill range feature to display for each year the list of all customers who were sold something during that year : =JOINTEXT(",", TRUE, FILTER(tabSales[Customer],tabSales[Year]=A2#)) (formula input in B2)

Unfortunately, this last formula does not work:

• Excel returns #N/A in B2
• B2 does not spill over B3, B4, ...

The lack of spilling makes me wonder whether the # reference within the FILTER function is recognized...

How could I get this formula to work ?

Sales table Report with TEXTJOIN function not working

Answer 1

In your A2, you could do:

=LET( data, tabSales[[Date]:[Customer]],
       sData, SORTBY(data,INDEX(data,,1),-1),
       yr, YEAR(INDEX(sData, , 1)), cust, INDEX(sData, , 2),
       uYrs, TRANSPOSE(SORT(UNIQUE(yr),,-1)),
       CTA, SORT(UNIQUE(TRANSPOSE(IF(yr=uYrs,cust,"")),TRUE),,,TRUE),
       cStr, LET( m, CTA,
                   rSeq, SEQUENCE( ROWS(m) ),
                   L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - 1,
                   i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
                  IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) ),
       CHOOSE( {1,2}, TRANSPOSE(uYrs), cStr ) )

where the inputs are the original sale dates and customer name as a dynamic array placed in data. This approach calculates the year from the date rather than taking that as an input simply because of the structure of tabSales lends to this approach.

This is a heavy solution and if it were me, I would have just taken your formula in B2 and dragged it down. But I stumbled on your question and realized its similarity to this question: Reference for a dynamic range which seems to be a general problem type. So, it seemed logical to put one solution inside of another.

The first part of the LET breaks down the data into customer and year and forms a sorted, unique array called CTA (customer timeline array). It then feeds this into cStr which does a row-by-row textjoin (in this case, it is year by year). The final line uses CHOOSE to merge the unique years (uYrs) with the cStr into a single dynamic array.

The result is a dynamic array that spills with years in descending sort and unique customer names concatenated by year. Only the latest year is sorted, but that was not a requirement. It could be a feature with a few more lines, but the formula is already quite long.

Nearly three months later, I am sure this is of no value, but out of curiosity, I got a working solution and thought perhaps it is worth posting. It is not the answer that matters in this case, but the method.