Delimiting a concatenated multi-column, multi-row output (TEXTJOIN + FILTER + INDEX + SEQUENCE)

[AOB]

I have a multi-column table listing (among other things) names and associated groups, á la :

Group	SomeField1	SomeField2	SomeField3	First Name	Last Name	SomeField4	SomeFieldX
A	foo	bar	foobar	Homer	Simpson	foo	bar
A	foo	bar	foobar	Fred	Flintstone	foo	bar
B	foo	bar	foobar	Peter	Griffin	foo	bar
B	foo	bar	foobar	Danger	Mouse	foo	bar
B	foo	bar	foobar	Yogi	Bear	foo	bar

I'm trying to put together a formula that returns a concatenated list of names (i.e. multiple columns from multiple matches into a single delimited list of names)

I have this so far :

=TEXTJOIN(" ",TRUE,FILTER(INDEX(tblMyTable,SEQUENCE(ROWS(tblMyTable)),{5,6}),tblMyTable[Group]=GroupName))

But that (obviously) just delimits each first and last name with a space, á la :

GroupName = "A" : Homer Simpson Fred Flintstone
GroupName = "B" : Peter Griffin Danger Mouse Yogi Bear

I'd like to delimit each name "pair" (e.g. First Name + Last Name) with a comma (or some separating character) to achieve something like this :

GroupName = "A" : Homer Simpson, Fred Flintstone
GroupName = "B" : Peter Griffin, Danger Mouse, Yogi Bear

Any suggestions as to how I could modify my formula to delimit the output in this manner?

Thanks!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Group	SomeField1	SomeField2	SomeField3	First Name	Last Name	SomeField4	SomeFieldX
2	A	foo	bar	foobar	Homer	Simpson	foo	bar
3	A	foo	bar	foobar	Fred	Flintstone	foo	bar
4	B	foo	bar	foobar	Peter	Griffin	foo	bar
5	B	foo	bar	foobar	Danger	Mouse	foo	bar
6	B	foo	bar	foobar	Yogi	Bear	foo	bar
7
8
9
10	A	Homer Simpson, Fred Flintstone
11	B	Peter Griffin, Danger Mouse, Yogi Bear
Data

Cell Formulas
Range	Formula
B10:B11	B10	=TEXTJOIN({" ",", "},,FILTER(CHOOSECOLS(tblMyTable,5,6),tblMyTable[Group]=A10))

 

[AOB]

Well that is pretty freaking awesome @Fluff thank you SO much - not only have you solved the problem, you've introduced me to CHOOSECOLS which I wasn't familiar with and, now, adore, and the formula is significantly simpler to read as well.

This is brilliant, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[AOB]

Sorry to resurrect this again @Fluff (and let me know if it should move to its own thread)

Been playing around with the formula and was curious to know if/how to use the CHAR() function within the delimiter array for the TEXTJOIN() function?

So, for example, if, instead of delimiting the "second" entry from the FILTER output with a comma :

=TEXTJOIN({" ",", "},,FILTER(CHOOSECOLS(tblMyTable,5,6),tblMyTable[Group]=A10))

One wanted to use a carriage return / line feed (i.e. a new line within the same cell) instead, the logical thing (to me) would be to replace the comma with CHAR(10), á la :

=TEXTJOIN({" ",CHAR(10)},,FILTER(CHOOSECOLS(tblMyTable,5,6),tblMyTable[Group]=A10))

But Excel doesn't like that ("There's a problem with this formula...")

How might one use an ASCII character in place of a standard character within that array?

 

[Fluff]

The {} is an array constant, so anything inside it must be a constant (not a formula).
How about

=TEXTJOIN(hstack(" ",CHAR(10)),,FILTER(CHOOSECOLS(tblMyTable,5,6),tblMyTable[Group]=A10))

 

[AOB]

Stunning Thank you!

 

[Fluff]

My pleasure.