Formula Help Please

[How_Do_I]

In Column B there can be a maximum of 12 unique numbers.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
D1 is a count of how many numbers in Column B
<o> </o>
In F1 I need a formula that will work regardless of how many of my 12 numbers are in Column B.
<o> </o>
So if I place 40 in B3, I would want F1 to = 23, 32, 40
<o> </o>
Can this be done please?

Excel Workbook
	A	B	C	D	E	F
1	1	23		2		23, 32
2	2	32
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
Sheet1

 

[MrKowz]

Try my ConcatRange function.

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

=CONCATRANGE(B1:B12,", ")

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

 

[How_Do_I]

Try my ConcatRange function.

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

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?

 

[MrKowz]

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.

 

[How_Do_I]

Still no luck, I'm still getting the #NAME? error

 

[How_Do_I]

I don't get VBA but I think the problem is...

type mismatch on the word delimiter...?

 

[MrKowz]

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:
   

   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,", ")

 

[How_Do_I]

Good Lord... that is just what I needed... Thank you very much for that!

 

[scottylad2]

Hi Mr Kowz, how can that be saved so that its available to all users as a function?

 

[meldoc]

Hi Deek,

Have a look at this, it should help.

http://www.rondebruin.nl/personal.htm