Concatenate values IF()

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

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.

"Nikki" wrote:

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

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)
--
Gary''s Student - gsnu200719

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


"D Cornett" wrote:

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.

"Nikki" wrote:

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

Simplify version:

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


"D Cornett" wrote:

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.

"Nikki" wrote:

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

On May 7, 12:04 pm, D Cornett
wrote:
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.



"Nikki" wrote:
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- Hide quoted text -

- 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

On May 7, 12:32 pm, Gary''s Student
wrote:
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)
--
Gary''s Student - gsnu200719

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

On May 7, 12:32 pm, Toppers wrote:
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



"D Cornett" wrote:
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.

"Nikki" wrote:

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- Hide quoted text -

- 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

On May 7, 12:32 pm, Gary''s Student
wrote:
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)
--
Gary''s Student - gsnu200719

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

D Cornett wrote...
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)," ",", ")