I'm trying to do the following:
- If ID is unique, then showing nothing in the "Result" column
- But, if the ID is not unique, then write all the Items for that ID separated by a comma (,)
- This condition will check for the range of row-1 t the current row. Not considered for the below row
- You can check the item for ID 1 as an example
I'm using the formula to solve this problem. To do that, I need to write the formula in each cell. But I want to solve this using the ARRAYFORMULA so I can write the formula in the 1st cell.
This is what I am using right now:
=TEXTJOIN(", ",TRUE,ARRAYFORMULA(IF(B2=$B$1:F1,$C$1:C1,"")))
CodePudding user response:
One way is to use REGEXREPLACE()
:
Formula in D2
:
=INDEX(IF(B2:B,IF(VLOOKUP(B2:B,B:C,2,0)=C2:C,"",REGEXREPLACE(TEXTJOIN(",",1,B2:B&"&"&C2:C),"\b(?:"&B2:B&"&"&C2:C&"\b.*|"&B2:B&"&([^,] ,)|.)","$1")&C2:C),""))