Is it possible to write ARRAYFORMULA of another ARRAYFORMULA

user_13434154 0
Viewed 102

I'm trying to do the following:

Problem Screen Shot

  • 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,"")))

You can see the file here: Check this file

Thank you in advance for your effort.

array-formulas google-sheets-formula google-sheets lookup regex
2 Answers

try:

=INDEX(SUBSTITUTE(SUBSTITUTE(IF(COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))>1, 
 REGEXEXTRACT(IFNA(VLOOKUP(A2:A&"", 
 TRIM(SPLIT(FLATTEN(QUERY(QUERY({A2:A&"×", SUBSTITUTE(B2:B, " ", "_"), ROW(B2:B)}, 
 "select max(Col2) where Col2 is not null group by Col3 pivot Col1"),,9^9)), "×")), 2, 0)), 
 TRIM(REPT("[\w]+ ", COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))))), ), " ", ", "), "_", " "))

enter image description here

user_56326290

One way is to use REGEXREPLACE():

enter image description here

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),""))
user_97581940
Related