The following formula works only if I manually fill down the column.
'=(TEXTJOIN("| ",TRUE,UNIQUE(FILTER(rngNOTES,(rngDWG=$H3)*(rngASSY=$I3)*(rngASSY_DESCR=$J3)))))
In H3, I have a UNIQUE spill range of multiple rows and three columns. I am trying to make the above equation spill down the column rather than having to copy down the column. I have tried the following:
=(TEXTJOIN("| ",TRUE,UNIQUE(FILTER(rngNOTES,(rngDWG=INDEX($H3#,,1))*(rngASSY=INDEX($H3#,,2))*(rngASSY_DESCR=INDEX($H3#,,4))))))
But, I receive an error (#N/A), which I believe is a result of differences in the size (Row numbers) of the UNIQUE Spill in $H3# and the ranges I am trying to evaluate against.
Is there a method to make this work, such that the column containing this formula is dynamic?
EDIT: Screen Cap of Sample file
pgSystemTester, I have a sample file ready; located here on my Google Drive.
If I update K3 to
=IF(INDEX(H3#,,1)<>"",(TEXTJOIN("| ",TRUE,UNIQUE(FILTER(rngNOTES,(rngDWG=$H3)*(rngASSY=$I3)*(rngASSY_DESCR=$J3))))),"")
I can get the formula to spill. But the criteria references remain static and do not increment to the "new" row reference. The resulting data is correct for only ROW(1) and spills to the other rows as ROW(1) data.
Which leads me to believe that I need to update the criteria in a way to reflect the new row location...this is where I am getting stuck.
Thank you, Randy
As per @pgSystemTester's comment, Textjoin won't spill on it's own as it's made to return a single result for whole arrays (in this case the array H3#).
To overcome this, you need to split your spilling array into rows, and apply the textjoin function to each row individually. Making the formula for a single row and manually copying it down is one option for this, but to spill it dynamically you can use the BYROW() function which is created to solve this exact problem.
=BYROW(H3#,LAMBDA(row,TEXTJOIN("|", TRUE, UNIQUE(FILTER(rngNOTES,(rngDWG=INDEX(row,1))*(rngASSY=INDEX(row,2))*(rngASSY_DESCR=INDEX(row,3)))))))
Note how I had to use INDEX(row, x) in the filters because it can't take separate arrays H3#, I3# and J3# (plus, H3# spans all three columns so the row array does as well).