If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Concatenate values IF()
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 "," "Nikki" wrote: 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 |
#12
|
|||
|
|||
Concatenate values IF()
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. -- Gary''s Student - gsnu200719 "Nikki" wrote: 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 |
#13
|
|||
|
|||
Concatenate values IF()
On May 7, 2:05 pm, Toppers wrote:
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 "," "Nikki" wrote: 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- Hide quoted text - - 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 |
#14
|
|||
|
|||
Concatenate values IF()
Nikki wrote...
.... 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),", ") |
#15
|
|||
|
|||
Concatenate values IF()
On May 7, 4:49 pm, Harlan Grove wrote:
Nikki wrote... ...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),", ") 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 |
#16
|
|||
|
|||
Concatenate values IF()
On May 7, 4:49 pm, Harlan Grove wrote:
Nikki wrote... ...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),", ") 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 |
#17
|
|||
|
|||
Concatenate values IF()
Nikki wrote:
.... 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? |
#18
|
|||
|
|||
Concatenate values IF()
Nikki wrote...
.... 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. |
#19
|
|||
|
|||
Concatenate values IF()
On 8 May 2007 09:16:43 -0700, Harlan Grove wrote:
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 |
#20
|
|||
|
|||
Concatenate values IF()
On May 8, 11:15 am, Harlan Grove wrote:
Nikki wrote: ...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? 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! |
Thread Tools | |
Display Modes | |
|
|