Return range of cell concatenaded based on single cell value

Rocas

New Member
Joined
Jul 2, 2012
Messages
10
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)
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,952
Office Version
  1. 365
Platform
  1. Windows
Download MoreFunc. Then with the number in B1, use this in C1.

Excel Workbook
ABC
1q90013q9002 q9003 q9004
2q9002**
3q9003**
4q9004**
5q9005**
6q9006**
Sheet1
 

Rocas

New Member
Joined
Jul 2, 2012
Messages
10
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
This could be a new UDF - User Defined Function:

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,414
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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...

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

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Rocas,

Welcome to the MrExcel forum.


If your raw data worksheet setur looked like this:


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





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


Excel Workbook
BCDEF
1Number of variable entered:4q9001 q9002 q9003 q9004
Sheet1





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


Excel Workbook
BCDEF
1Number of variable entered:9q9001 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


Code:
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

New Member
Joined
Jul 2, 2012
Messages
10
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Rocas,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,888
Messages
5,544,874
Members
410,642
Latest member
Launayvolone
Top