Return range of cell concatenaded based on single cell value

[Rocas]

Hello everybody,

I´m trying to work out the following problem.

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001

q9002

q9003

q9004

q9005

q9006

<tbody>
</tbody>

etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.

Hopefully somebody can point me in the right direction. Is this doable using only formulas or should I use VBA? If so, for what kind of macro function should I be searching for on the internet? I hope somebody can help me on my way and tell me where I should start searching.

Thanks in advance, all your help is appreciated.

(Using Excel 2010)

 

[BenMiller]

Download MoreFunc. Then with the number in B1, use this in C1.

Excel Workbook
	A	B	C
1	q9001	3	q9002 q9003 q9004
2	q9002	*	*
3	q9003	*	*
4	q9004	*	*
5	q9005	*	*
6	q9006	*	*
Sheet1

 

[Rocas]

Thanks a lot Ben. I installed this feature and it worked instantly!

However, maybe your were expecting this. Due to company policy I can not use this feature on my work desktop. I am bound to Excel and VBA to solve this problem. Is there a way to recreate this function in VBA?

Thanks again for your swift reply and sorry for making this so difficult.

 

[wigi]

This could be a new UDF - User Defined Function:

Function ConcatCells(rngBeginCell As Range, siNumberOfCells As Single) As String

    ConcatCells = Join(Application.Transpose(rngBeginCell.Resize(siNumberOfCells)))


End Function

Add the code to a module in your workbook.
You can use the function in your Excel sheet as:
=ConcatCells(A1,5)

This would be the case of the q-numbers starting in cell A1 (first parameter) down that column A, and you would want to grab 5 of them (second parameter).

That 5 could also be a reference to another cell (cell C10 for example).

=ConcatCells(A1,C10)

 

[Rick Rothstein]

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001

q9002

q9003

q9004

q9005

q9006

<TBODY>
</TBODY>

etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

Give this macro a try...

Sub CombineNames()
  Range("C1").Value = Join(WorksheetFunction.Transpose(Range("A1").Resize(Range("B1"))))
End Sub

 

[hiker95]

Rocas,

Welcome to the MrExcel forum.


If your raw data worksheet setur looked like this:

Excel Workbook
	A	B	C	D	E	F
1	q9001	Number of variable entered:
2	q9002
3	q9003
4	q9004
5	q9005
6	q9006
7	q9007
8	q9008
9	q9009
10	q9010
11	q9011
12	q9012
13	q9013
14	q9014
15	q9015
16	q9016
17	q9017
18	q9018
19	q9019
20	q9020
21	q9021
22	q9022
23	q9023
24	q9024
25	q9025
26	q9026
27	q9027
28	q9028
29	q9029
30	q9030
31	q9031
32	q9032
33	q9033
34	q9034
35	q9035
36	q9036
37	q9037
38	q9038
39	q9039
40	q9040
41	q9041
42	q9042
43	q9043
44	q9044
45	q9045
46	q9046
47	q9047
48	q9048
49	q9049
50	q9050
Sheet1

And you entered 4 in cell E1, you would get this:

Excel Workbook
	B	C	D	E	F
1	Number of variable entered:			4	q9001 q9002 q9003 q9004
Sheet1

And you entered 9 in cell E1, you would get this:

Excel Workbook
	B	C	D	E	F
1	Number of variable entered:			9	q9001 q9002 q9003 q9004 q9005 q9006 q9007 q9008 q9009
Sheet1

If you deleted what is in cell E1, or you tried to enter a number less than 1 or greater than 50, or a letter character you would get this message:

You must enter a number from 1 to 50 - macro terminated!





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
Dim lr As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Range("F1").ClearContents
  If Target = "" Then
    Range("E1:F1").ClearContents
    MsgBox "You must enter a number from 1 to 50 - macro terminated!"
  ElseIf Not IsNumeric(Target) Then
    Range("E1:F1").ClearContents
    MsgBox "You must enter a number from 1 to 50 - macro terminated!"
  ElseIf Target.Value >= 1 And Target.Value <= 50 Then
    lr = Target.Value
    If lr = 1 Then
      Range("F1") = Range("A1")
    ElseIf lr > 1 And lr <= 50 Then
      Range("F1") = Join(Application.Transpose(Range("A1:A" & lr)), " ")
    End If
  ElseIf Target.Value < 1 Or Target.Value > 50 Then
    Range("E1:F1").ClearContents
    MsgBox "You must enter a number from 1 to 50 - macro terminated!"
  End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then make changes to cell E1.

 

[Rocas]

Wigi, Rick and Hiker thank you all for your help. This is amazing, I didn't expect this much replies.

Hiker I have used your code. It works perfectly! Thanks again for the solution and easy explanation.

 

[hiker95]

Rocas,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.