How to produce concatenated cell drawing from a changing source range

d0rian

Board Regular
Joined
May 30, 2015
Messages
241
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
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
ABCDE
1GroupSym
21a1
31b2e
41c3fgh
51d
62e
73f
83g
93h
10
Concat groups
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
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)

Code:
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
ABCDE
1GroupSym
21a1
31b2e
41c3fgh
51d
62e
73f
83g
93h
10
Concat groups
 
Last edited:

d0rian

Board Regular
Joined
May 30, 2015
Messages
241
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).
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
.. 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
ABC
1GroupSym
21a
31ba b
41ca b c
51da b c d
62ee
73ff
83gf g
93hf g h
Concat groups
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
.. 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
ABCDEF
1GroupSym
21a1
31bb c d2e
41cc d3f g h
51dd
62ee
73ff g h
83gg h
93hh
Concat groups (2)
 
Last edited:

Forum statistics

Threads
1,082,305
Messages
5,364,400
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top