How to produce concatenated cell drawing from a changing source range

[d0rian]

See image below. I have several groupings of stock symbols -- the group they belong to changes on a daily basis, so the GROUP value in column A will change regularly. I want a formula that will produce the values in cells E2, E3, and E4...in other words, I want all of the symbols in "Group 1" concatenated (with a double-space in between each)...any help? I can't just use the clunky formulas =A1&" "&A2&" "&A3, etc etc because the # of symbols in each Group (in column A) changes...so there won't always be 16 symbols in group 1, 9 in Group 2, and so on...the formula needs to dynamically adjust to produce concatenated values based on the # of symbols in each group...

 

[Peter_SSs]

If you have the TEXTJOIN function available you can use this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

If you don't have TEXTJOIN then I would suggest a macro solution. Post back if you want/need that.

Excel Workbook
	A	B	C	D	E
1	Group	Sym
2	1	a		1	abcd
3	1	b		2	e
4	1	c		3	fgh
5	1	d
6	2	e
7	3	f
8	3	g
9	3	h
10
Concat groups

 

[Peter_SSs]

If you don't have TEXTJOIN then I would suggest a macro solution. Post back if you want/need that.

I'll post this user-defined function anyway. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Symbols(rGroup As Range, sGroup As String, rSym As Range) As String
  Symbols = Join(Filter(Application.Transpose(Evaluate("if(" & rGroup.Address & "=" & sGroup & "," & rSym.Address & ",""@"")")), "@", False), "  ")
End Function

Excel Workbook
	A	B	C	D	E
1	Group	Sym
2	1	a		1	abcd
3	1	b		2	e
4	1	c		3	fgh
5	1	d
6	2	e
7	3	f
8	3	g
9	3	h
10
Concat groups

 

[d0rian]

Thanks for the reply; I'm trying to stay away from Array formulas, since my sheet is rather bloated and speed is important and Arrays are dynamic / more resource-intensive...likewise, I'm not all that great with VBA, so would prefer an in-cell formulaic solution...does anything jump to mind, or from what I described will I probably have to use at least one of those things? (I'm using v2007, so don't have TEXTJOIN).

 

[Peter_SSs]

.. or from what I described will I probably have to use at least one of those things?

I think you will.

(I'm using v2007, so don't have TEXTJOIN).

Then unless somebody come up with a better solution, I think you'll need the UDF. If you follow those steps I gave you it should be easy enough.

What might be the largest number of rows in a single group?

The only other thing I can think of with standard formulas is as below where the result for each group appears on the last row of the group rather than as a compact result table.

Excel Workbook
	A	B	C
1	Group	Sym
2	1	a	a
3	1	b	a b
4	1	c	a b c
5	1	d	a b c d
6	2	e	e
7	3	f	f
8	3	g	f g
9	3	h	f g h
Concat groups

 

[Peter_SSs]

.. or if we turn it up the other way. Copy these formulas down (& perhaps hide column C).
You'd have to get the list of groups some other way but perhaps you already have that?

Excel Workbook
	A	B	C	D	E	F
1	Group	Sym
2	1	a	a b c d		1	a b c d
3	1	b	b c d		2	e
4	1	c	c d		3	f g h
5	1	d	d
6	2	e	e
7	3	f	f g h
8	3	g	g h
9	3	h	h
Concat groups (2)