Formula Help Please

How_Do_I

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,843
Office Version
  1. 2010
Platform
  1. Windows
In Column B there can be a maximum of 12 unique numbers.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
D1 is a count of how many numbers in Column B
<o:p> </o:p>
In F1 I need a formula that will work regardless of how many of my 12 numbers are in Column B.
<o:p> </o:p>
So if I place 40 in B3, I would want F1 to = 23, 32, 40
<o:p> </o:p>
Can this be done please?

Excel Workbook
ABCDEF
1123223, 32
2232
33
44
55
66
77
88
99
1010
1111
1212
Sheet1
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Try my ConcatRange function.

Place this into a module in the worksheet, and use the formula:

=CONCATRANGE(B1:B12,", ")

Code: 
Public Function Concatrange(rng As Range, Optional delimiter As String)
   Dim rRng As Range
   
   If delimiter Is Nothing Then
      delimiter = ""
   End If
   
   For Each rRng In rng
      If rRng.Value <> "" Then
         Concatrange = Concatrange & rRng.Value & delimiter
      End If
   Next rRng
   
   If Len(Concatrange) > 0 Then
      Concatrange = Left(Concatrange, Len(Concatrange) - Len(delimiter))
   Else
      Concatrange = ""
   End If
   
End Function
 
Upvote 0
MrKowz said:
Try my ConcatRange function.

Place this into a module in the worksheet, and use the formula:
Click to expand...

Thanks for your help but I'm getting the #NAME? Error... I right clicked my TAB on the worksheet, selected view code and placed your VBA there... Is that right please?
 
Upvote 0
Once you are in the VBA editor, go to Insert>Module and paste it in a module. Functions can't be stored at the worksheet (or workbook) level.
 
Upvote 0
Still no luck, I'm still getting the #NAME? error :(
 
Upvote 0
I don't get VBA but I think the problem is...

type mismatch on the word delimiter...?
 
Upvote 0
Follow these steps exactly (there was also a small error in the code that I fixed):

  1. From your worksheet, go to Tools>Macro>Visual Basic Editor
  2. In the VBA Editor, go to Insert>Module
  3. Paste this code into the new window that pops up:
    Code: 
    Public Function Concatrange(rng As Range, Optional delimiter As String)
   Dim rRng As Range
   
   For Each rRng In rng
      If rRng.Value <> "" Then
         Concatrange = Concatrange & rRng.Value & delimiter
      End If
   Next rRng
   
   If Len(Concatrange) > 0 Then
      Concatrange = Left(Concatrange, Len(Concatrange) - Len(delimiter))
   Else
      Concatrange = ""
   End If
   
End Function
  4. In your worksheet, type =CONCATRANGE(B1:B12,", ")
 
Upvote 0
Good Lord... that is just what I needed... Thank you very much for that! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
258
Fluff
Fluff
Dan Wilson
  • Solved
Formula help.
2
Replies
10
Views
241
Jeffrey Mahoney
Jeffrey Mahoney
N
  • Question Question
Help creating a formula
2
Replies
11
Views
527
Fluff
Fluff
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
400
dshtesy
D
S
  • Solved
Few problems with dynamic arrays
2
Replies
15
Views
323
djclements
djclements

Forum statistics

Threads
1,224,616
Messages
6,179,910
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top