Textjoin values of column B if duplicates are present in column A-CodePudding

I want to consolidate the data of column B into a single cell ONLY IF the index (ie., Column A) is duplicated.

For example:

Currently, I'm doing manually for each duplicated index by using the following formula:

=TEXTJOIN(", ",TRUE,B4:B6)

Is there a better way to do this all at once?

Any help is appreciated.

CodePudding user response：

There may easier way but you can try this formula-

=BYROW(A2:A17,LAMBDA(p,IF(INDEX(MAP(A2:A17,LAMBDA(x,SUM(--(A2:INDEX(A2:A17,ROW(x)-1)=x)))),ROW(p)-1,1)=1,TEXTJOIN(", ",1,FILTER(B2:B17,A2:A17=p)),"")))

CodePudding user response：

Using REDUCE might be possible for a more succinct solution, though try this for now:

=BYROW(A2:A17,LAMBDA(ζ,LET(α,A2:A17,IF((COUNTIF(α,ζ)>1)*(COUNTIF(INDEX(α,1):ζ,ζ)=1),TEXTJOIN(", ",,FILTER(B2:B17,α=ζ)),""))))