Concatenate values IF()

[Nikki]

Hello group, we meet again:

I have values returned, in Column W25:41; either a number (formatted
as text) or nothing (""), depending on checkboxes selected (Thanks
again to all who helped with that problem before). However, I could
change the "nothing" value if needed... to make this problem easier to
solve.

Next step, I'd like to concatenate these to a singe cell with a comma
delimeter. Problem is, I don't want to include ", , , ," for the blank
values. I know I might have to work with some sort of IF()
statement(s) and either the CONCATENATE() or "&" function, but haven't
quite figured it out. Maybe a LOOKUP() or ROW(), to find the max
value? Those have been my brainstorms, might be on the right track,
might not.

It might be of help to know the last value of actual text in the
column is always the highest number... the highest value possible is
10.

Note, there are a total of 17 rows that might or might not have
values... eliminating too many nested functions.

Yes, this could be done a bit messily by using a series of cells for
reference in between, but I'd like to make this as clean as possible.

Thank you in advance,
Nikki

 

[Guest]

Nikki,
I suggest that you use an IF statement that includes your "," with your
criteria. For example, =IF(A1&A2="","",IF(A1="","",A1) &
IF(A2="","",IF(A1="",A2,", " & A2))). This formula returns a blank if both A1
& A2 are blank. Or it returns "contents A1", "contents A2", or "contents A1,
contents A2". If you have more than two cells, this can get quite detailed
but can still work.

 

[Guest]

Try this UDF:

Function list_um(rr As Range) As String
list_um = ""
gotit = False
For Each r In rr
If IsEmpty(r) Or r.Value = "" Then
Else
If gotit Then
list_um = list_um & "," & r.Value
Else
gotit = True
list_um = r.Value
End If
End If
Next
End Function

use it like:

=list_um(W25:W41)

 

[Guest]

Try this UDF:

in your cell put:

=myconcatenate(W25:W41,",")

Function myconcatenate(rng As Range, delimeter As String)
Mystring = ""
For Each cell In rng
If cell <> "" Then
Mystring = Mystring & cell.Value & delimeter
End If
Next
myconcatenate = Left(Mystring, Len(Mystring) - 1)
End Function

 

[Guest]

Simplify version:

=IF(COUNT(A1:A2)=2,A1&","&A2,A1&A2)

 

[Nikki]

Nikki,
I suggest that you use an IF statement that includes your "," with your
criteria. For example, =IF(A1&A2="","",IF(A1="","",A1) &
IF(A2="","",IF(A1="",A2,", " & A2))). This formula returns a blank if both A1
& A2 are blank. Or it returns "contents A1", "contents A2", or "contents A1,
contents A2". If you have more than two cells, this can get quite detailed
but can still work.











- Show quoted text -

D,

Thank you for your suggestion, but being that I might have a
possibility of 17 values, this seems a bit complicated.

Nikki

 

[Nikki]

Try this UDF:

Function list_um(rr As Range) As String
list_um = ""
gotit = False
For Each r In rr
If IsEmpty(r) Or r.Value = "" Then
Else
If gotit Then
list_um = list_um & "," & r.Value
Else
gotit = True
list_um = r.Value
End If
End If
Next
End Function

use it like:

=list_um(W25:W41)

This works great, thank you. I've never had the opportunity to work
with UDF's before, I can see I have some learning to do.

Thanks again,
Nikki

 

[Nikki]

Try this UDF:

in your cell put:

=myconcatenate(W25:W41,",")

Function myconcatenate(rng As Range, delimeter As String)
Mystring = ""
For Each cell In rng
If cell <> "" Then
Mystring = Mystring & cell.Value & delimeter
End If
Next
myconcatenate = Left(Mystring, Len(Mystring) - 1)
End Function






- Show quoted text -

This works great, except it returns a "," at the end of the list,
something that's not necessary. I'll go with the suggestion by Gary's
student.

Once again, I'll have to work on my VBA skills to decode what the two
of you thought of.

Thanks again,
Nikki

 

[Nikki]

Try this UDF:

Function list_um(rr As Range) As String
list_um = ""
gotit = False
For Each r In rr
If IsEmpty(r) Or r.Value = "" Then
Else
If gotit Then
list_um = list_um & "," & r.Value
Else
gotit = True
list_um = r.Value
End If
End If
Next
End Function

use it like:

=list_um(W25:W41)

~~Okay, another hitch that might make this problem a bit harder to
solve~~

There is also an "Other" box that, when checked, links a series of
values (or just one value), formatted as text (comma or comma-space
delimited), to W43. These values are supposed to be the values
available in addition to the values returned from the excellent UDF
recently provided.

Now, I'd like to put these "values" in order, if that's possible. For
example, returned from the UDF is:
"0.5, 0.625, 1, 2, 3.75, 4, 6, 7, 7.5, 8, 9"
Returned in W43:
"2.4, 6.7" -- or any other user-defined string

I'd like to return a text string with all the values combined - in
numerical order:
"0.5, 0.625, 1, 2, 2.4, 3.75, 4, 6, 6.7, 7, 7.5, 8, 9"

What makes this difficult to solve in my mind is putting values in
numerical order if they're interpreted as text...

Would this require another UDF or could it be done with various Text
Functions?

Thank you again for all your help,
Nikki

 

[Harlan Grove]

I suggest that you use an IF statement that includes your "," with your
criteria. . . .

As long as there wouldn't be space characters in any of the cells,
simpler to use something like

=SUBSTITUTE(TRIM(A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6&" "&A7&" "
&A8&" "&A9&" "&A10&" "&A11&" "&A12&" "&A13&" "&A14&" "&A15&" "
&A16&" "&A17)," ",", ")

 

[Guest]

Nikki,
It shouldn't (doesn't in my testing) return "," at end of string.

myconcatenate = Left(Mystring, Len(Mystring) - 1)

This statement removes the last ","

 

[Guest]

Its not difficult in theory, we would just bring the values into a VBA array
and then sort them in VBA (either as text or numbers), then concatenate them
in sorted order and have the function return the sorted string.

I am not at the Help Center now. Check back tomorrow.

 

[Nikki]

Nikki,
It shouldn't (doesn't in my testing) return "," at end of string.

myconcatenate = Left(Mystring, Len(Mystring) - 1)

This statement removes the last ","








- Show quoted text -

Toppers,

The reason why it was returning a "," at the end of the string was
because I changed the delimiter argument to ", " to allow a space
between numbers. I changed the line to: myconcatenate = Left(Mystring,
Len(Mystring) - 2) to allow for this... and it works great now.

I can see there is more than one way to "skin a cat", both UDF's work
great.

Thanks again,
Nikki

 

[Harlan Grove]

There is also an "Other" box that, when checked, links a series of
values (or just one value), formatted as text (comma or comma-space
delimited), to W43. These values are supposed to be the values
available in addition to the values returned from the excellent UDF
recently provided.

....

One way - a customized udf.


Function foo(delim As String, ParamArray a()) As String
Dim x As Variant, y As Variant, z As Variant
Dim k As Long, t As String

For Each x In a
If TypeOf x Is Range Then y = x.Value

If Not IsArray(x) Then y = Array(x)

For Each z In y
If CStr(z) <> "" Then
t = t & delim & CStr(z)
End If
Next z

Next x

x = Split(Mid(Replace(t, Trim(delim), vbLf), 2), vbLf)
ReDim y(1 To UBound(x) - LBound(x) + 1) As Double
t = ""

For Each z In x
k = k + 1
If IsNumeric(z) Then y(k) = CDbl(z)
Next z

ReDim Preserve y(1 To k)

For k = k To 1 Step -1
t = delim & CStr(Application.WorksheetFunction.Small(y, k)) & t
Next k

foo = Mid(t, Len(delim) + 1)
End Function


Another way, using the MOREFUNC.XLL add-in, which is available at

http://xcell05.free.fr/english/

=MCONCAT(HSORT(EVAL("{"&SUBSTITUTE(TRIM(MCONCAT(A1:A17," ")),
" ",",")&","&W34&"}"),,1),", ")

 

[Nikki]

...>There is also an "Other" box that, when checked, links a series of

...

One way - a customized udf.

Function foo(delim As String, ParamArray a()) As String
Dim x As Variant, y As Variant, z As Variant
Dim k As Long, t As String

For Each x In a
If TypeOf x Is Range Then y = x.Value

If Not IsArray(x) Then y = Array(x)

For Each z In y
If CStr(z) <> "" Then
t = t & delim & CStr(z)
End If
Next z

Next x

x = Split(Mid(Replace(t, Trim(delim), vbLf), 2), vbLf)
ReDim y(1 To UBound(x) - LBound(x) + 1) As Double
t = ""

For Each z In x
k = k + 1
If IsNumeric(z) Then y(k) = CDbl(z)
Next z

ReDim Preserve y(1 To k)

For k = k To 1 Step -1
t = delim & CStr(Application.WorksheetFunction.Small(y, k)) & t
Next k

foo = Mid(t, Len(delim) + 1)
End Function

Another way, using the MOREFUNC.XLL add-in, which is available at

http://xcell05.free.fr/english/

=MCONCAT(HSORT(EVAL("{"&SUBSTITUTE(TRIM(MCONCAT(A1:A17," ")),
" ",",")&","&W34&"}"),,1),", ")

Harlan,

I tried the UDF to no avail.

String of other values: W43 = 1.2, 5.6
Array: W25:W41 = 0.5, 0.625, 1, 2, 3.75, 4, 6, 7, 7.5, 8, 9 -- in
addition to 6 blank cells
Returned string: AA32 = "0.51.2, 5.60.6251.2, 5.611.2, 5.621.2,
5.63.751.2, 5.641.2, 5.661.2, 5.671.2, 5.67.51.2, 5.681.2, 5.69"
Wanted returned string: "0.5, 0.625, 1, 1.2, 2, 3.75, 4, 5.6, 6, 7,
7.5, 8, 9"

Thank you though,

I will try the link to the function you listed, perhaps that will have
better results.

Nikki

 

[Nikki]

...>There is also an "Other" box that, when checked, links a series of

...

One way - a customized udf.

Function foo(delim As String, ParamArray a()) As String
Dim x As Variant, y As Variant, z As Variant
Dim k As Long, t As String

For Each x In a
If TypeOf x Is Range Then y = x.Value

If Not IsArray(x) Then y = Array(x)

For Each z In y
If CStr(z) <> "" Then
t = t & delim & CStr(z)
End If
Next z

Next x

x = Split(Mid(Replace(t, Trim(delim), vbLf), 2), vbLf)
ReDim y(1 To UBound(x) - LBound(x) + 1) As Double
t = ""

For Each z In x
k = k + 1
If IsNumeric(z) Then y(k) = CDbl(z)
Next z

ReDim Preserve y(1 To k)

For k = k To 1 Step -1
t = delim & CStr(Application.WorksheetFunction.Small(y, k)) & t
Next k

foo = Mid(t, Len(delim) + 1)
End Function

Another way, using the MOREFUNC.XLL add-in, which is available at

http://xcell05.free.fr/english/

=MCONCAT(HSORT(EVAL("{"&SUBSTITUTE(TRIM(MCONCAT(A1:A17," ")),
" ",",")&","&W34&"}"),,1),", ")

Harlan,

Brilliant!! The add-in works perfectly.

One potential problem... if another user of the program does not have
this add-in installed, would this work for them? This program is being
created for co-workers for their ease of use. In addition, the program
might be used on multiple computers - laptops, convertibles, tablets,
etc while we go to outreach centers. If the add-in has to be installed
on each one of these systems that would make this a bit of a
headache.

Thank you again,
Nikki

 

[Harlan Grove]

I tried the UDF to no avail.

String of other values: W43 = 1.2, 5.6
Array: W25:W41 = 0.5, 0.625, 1, 2, 3.75, 4, 6, 7, 7.5, 8, 9 -- in
addition to 6 blank cells
Returned string: AA32 = "0.51.2, 5.60.6251.2, 5.611.2, 5.621.2,
5.63.751.2, 5.641.2, 5.661.2, 5.671.2, 5.67.51.2, 5.681.2, 5.69"
Wanted returned string: "0.5, 0.625, 1, 1.2, 2, 3.75, 4, 5.6, 6, 7,
7.5, 8, 9"

....

I had tested the udf. For me, the formula

=foo(", ",W25:W41,W43)

returns

0.5, 0.625, 1, 1.2, 2, 3.75, 4, 5.6, 6, 7, 7.5, 8, 9

What version of Excel are you using?

 

[Harlan Grove]

One potential problem... if another user of the program does not have
this add-in installed, would this work for them? This program is being
created for co-workers for their ease of use. In addition, the program
might be used on multiple computers - laptops, convertibles, tablets,
etc while we go to outreach centers. If the add-in has to be installed
on each one of these systems that would make this a bit of a
headache.

If this is for co-workers, then each of them would have to have the
MOREFUNC.XLL add-in or you're back to needing to use udfs.

 

[Ron Rosenfeld]

If this is for co-workers, then each of them would have to have the
MOREFUNC.XLL add-in or you're back to needing to use udfs.

Harlan,

There is an option to 'embed' morefunc.xll with the workbook. With a
"standard" installation, it is in the Tools/morefunc menu. I've not tried it
so cannot confirm that it works, but it is supposed to.

Wouldn't that work in the OP's situation?
--ron

 

[Nikki]

...>I tried the UDF to no avail.


...

I had tested the udf. For me, the formula

=foo(", ",W25:W41,W43)

returns

0.5, 0.625, 1, 1.2, 2, 3.75, 4, 5.6, 6, 7, 7.5, 8, 9

What version of Excel are you using?

It works, user error... I was using the wrong syntax.

I will go with this UDF since the add-in requires additional
installs.

Thank you very much!