array v concatenate including a ", " seperator

[PaulBr2]

I am trying to write an array formula that is versatile to work a variable number of adjacent cells to be part of the array (click and drag to highlight is fine) but need to ensure each cell content is seperated with a comma then space before the next cell contents are included.
The issue I am having or trying to solve is that the concatenated content is from a variable number of adjacent cells. Which is why I was looking at array instead. but the seperator has to be a comma then space.
Any help for an excel dufus would be super appreciated....

 

[Fluff]

Hi & welcome to MrExcel.
How about in X2 copied down

=TEXTJOIN(", ",,FILTER($Y$2:$Y$100,$A$2:$A$100=A2))

 

[PaulBr2]

Hi & welcome to MrExcel.
How about in X2 copied down

=TEXTJOIN(", ",,FILTER($Y$2:$Y$100,$A$2:$A$100=A2))

Thanks, but I maybe didn't explain it properly. each true cell (vertically in column v) needs have the adjacent cell populated with the concatenated contents of column y until the next true cell in column v then it begins again..

 

[Fluff]

In that case try

=IF(V2=TRUE,TEXTJOIN(", ",,FILTER($Y$2:$Y$100,$A$2:$A$100=A2)),"")

 

[PaulBr2]

Thanks, but I maybe didn't explain it properly. each true cell (vertically in column v) needs have the adjacent cell populated with the concatenated contents of column y until the next true cell in column v then it begins again..

Great thanks, but as you can see from the image above the green empty cell (X23) must then have the comma space separated cell contents from Y23:Y26. So the column references stay the same but the row references are dynamic and linked to the blank cells (vertically) between the TRUE cells.

 

[Fluff]

Have you tried what I suggested?

 

[PaulBr2]

Have you tried what I suggested?

Yes, but as the cell references are constants (using the $ to fix to the exact array in the formula) it returns a calc error.

 

[PaulBr2]

I don't want the entire contents of column y in one cell.. it's dynamic based on the range of the true+empty cells (row reference) below the true cell. Then it starts again with the next true cell and a new row reference.

 

[Fluff]

I don't want the entire contents of column y in one cell

You won't get that.

it returns a calc error.

I don't see how you can get that. Did you put the formula in X2 & copy down as suggested?

 

[PaulBr2]

You won't get that.


I don't see how you can get that. Did you put the formula in X2 & copy down as suggested?

Yes, I did exactly that. Copy and paste either as a full pull down to the bottom of the sheet (which is 2300+ rows) or by copy and paste individually into the cells with a true cell to the left. But it is also referencing column A which has no relevance to the required input/output. From my understanding the $ symbol maintains a fixed start cell and end cell to the range/array in the formula you posted, so the formula will always refer to those ranges. regardless of where it is copy/pasted to.