1

On column D i want TEXTJOIN of column C.

  1. delimiter is comma with a space after: ", "
  2. the two conditions that have to be met are: same date in column A, same value in column B
  3. This is a table so I prefer using references of the name of the columns (for example "Date")

any help will be greatly appreciated

enter image description here

Kobe2424
  • 147
  • 7

2 Answers2

2

=IF($B1<>$B2,IFERROR(TEXTJOIN(",",0,$C2:INDEX($C2:$C$12,MATCH(1,--($B2:$B$12<>$B2),0)-1)),TEXTJOIN(",",0,$C2:$C$12)),"")

The Formula indexes column C from the current row up until it finds a different value in column B (minus 1). The IFERROR is for the last found in range, because it will not find a value in the range after that value that doesn't match that value anymore. To be entered with ctrl+shift+enter for Excel versions prior to Office 365

P.b
  • 8,293
  • 2
  • 10
  • 25
1

You can use TEXTJOIN in conjunction with FILTER.

Something like: =TEXTJOIN(",",1, FILTER($C$2:$C$12, ($B$2:$B$12=B2)*($A$2:$A$12=A2))

Note you can replace the ranges with the named ranges, and that '*' is used as a sort of AND within the FILTER function.

EDIT: to avoid repeating rows, you can wrap the entire function with an IF statement that checks for duplicates. Something like:

=IFERROR(IF(MATCH(
TEXTJOIN(",",1,FILTER($C$2:$C$12,($B$2:$B$12=$B12)*($A$2:$A$12=$A12))),D$1:D11,0)>0,""),
TEXTJOIN(",",1,FILTER($C$2:$C$12,($B$2:$B$12=$B12)*($A$2:$A$12=$A12))))

enter image description here

EDS
  • 2,155
  • 1
  • 6
  • 21
  • thank you :) very clear I wrote this to write it only for the some of the rows: =IF(COUNTIF(C$2:C2,C2)=1,TEXTJOIN(",",1,FILTER([filename],([Fruit]=[@Fruit])*([Date]=[@Date]))),"") I wonder if I can change the COUNTIF(C$2:C2,C2) to table reference as well – Kobe2424 Jul 18 '21 at 16:13
  • No problem, see my edit for another way to avoid duplicates – EDS Jul 18 '21 at 16:16