CODEWITHSUNDEEP
excelexcel-formulacountiftextjoinexcel-tables
Kobe2424
Kobe2424

Reputation: 169

TEXTJOIN of column with same values of other columns, Table references

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

Upvotes: 1

Views: 1633

Answers (2)

P.b
P.b

Reputation: 11483

=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

Upvotes: 2

EDS
EDS

Reputation: 2195

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

Upvotes: 1

Related Questions