0

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
laurent
  • 5
  • 1
  • 3
  • Just for your information; this can be nicely done through PowerQuery if you want this done on a report sheet. For example, see [this](https://stackoverflow.com/a/60772717/9758194). – JvdV Apr 21 '21 at 06:29
  • Sure. The thing is I want to avoid using PowerQuery (which I personally use on an almost daily basis and am in love with!) as people in my company who will take over the Excel spreadsheet once it is up and running don't know how to use it ! – laurent Apr 21 '21 at 06:36
  • @laurent Your formula is fine without `#` symbol. So, when filter function will return more than 1 record then `#` feature can not work as it will go next cell automatically where data is already exist. Try without `#` and it will work fine. – Harun24hr Apr 21 '21 at 07:01
  • Thanks Harun, the formula actually works. However it does not spill over the next rows like in column A. How can I make the spilling happen ? – laurent Apr 21 '21 at 07:35
  • You can't. Textjoin accepts arrays as arguments and returns a single text value. You will need to copy the formula down. There is no way to spill text join. – Scott Craner Apr 21 '21 at 13:00

1 Answers1

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.

result

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.

mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23
  • Hi Mark. Nearly a year later, thanks (and well done !) for your solution. I had ceased to believe that this problem could be solved :-) – laurent May 19 '22 at 22:56
  • Bonjour @laurent! C'est tres drôle! Alors, et depuis 8 Fevrier, il est plus facile grace a des fonctions "LAMBDA helpers". Le calcul de **cStr** ne nécessite qu'une seule ligne de code. – mark fitzpatrick May 23 '22 at 15:51