TEXT STRING separated by commas

[aka_krakur]

I have a defined name range (let's just call it INCIDENT
it selects a range in column A of all incidents it finds
So let's say the defined range is A4:A7
looks like this:
92980
93879
94349
95287

I would like to concatenate based off the defined name range with a final result looking like this:
92980, 93879, 94349, 95287

note: this range can grow or shrink based off a query refresh.

The defined name INCIDENT is always correct after the refresh.
I just need to find a way of putting this range into one (1) cell with commas separating them.

Is this possible

 

[Scott Huish]

If you download and install the free MOREFUNC add-in available here:
http://xcell05.free.fr/

=MCONCAT(INCIDENT,", ")

 

[jindon]

an UDF
1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11
Use in cell like
=krakur(INCIDENT,", ")

Function krakur(rng As Range, delim As String) As String
   krakur = Join(WorksheetFunction.Transpose(rng.Value),delim)
End Function

 

[danial]

an UDF
1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11
Use in cell like
=krakur(INCIDENT,", ")

Function krakur(rng As Range, delim As String) As String
   krakur = Join(WorksheetFunction.Transpose(rng.Value),delim)
End Function

Work...

 

[aka_krakur]

Thank you. Any chance you would have a clue how to use that string in a VB that is basically writing the SQL for a query?
I already have this posted at:
http://www.mrexcel.com/board2/viewtopic.php?t=292056

Let me know...thanks..that VB helped a lot, because not everyone has the "ADD-IN" that HOTPEPPER posted (even though that worked too). Now this VB will be able to allow any user to open the spreadsheet & refresh.

 

[Scott Huish]

It's irrelevant whether everyone has that add-in or not, as only you would need to download and install it, as MOREFUNC can be embedded in the workbook.
(Tools.Morefunc.Embed Morefunc in the Workbook)

 

[aka_krakur]

awh, didn't know that. Because I know that for formulas such as =NETWORKDAYS(A5,A6)
the user has to have the add-in (that's already part of Excel) checked or else it returns a value of #NUM# or #Value# or something like that.

Definitely wasn't trying to knock your help..I appreciated it..because it definitely got what I needed done today.

Thanks!!!

 

[Stephen_Tsui]

A VB programme can done this effect

See if it is useful in your situation, if not plz let know, thx.

Copy this code to VBE in the workbook that you send to other.

If you want to use it, just select a cell and input the formula

=StringCompound(@)

(@ = Range you required)

Function StringCompound(myRang As Range)

Dim msg As String

msg = myRang.Cells(1)

For i = 2 To myRang.Cells.Count
    msg = msg & "," & myRang.Cells(i)
Next
StringCompound = msg

End Function

 

[aka_krakur]

Got it to work...thanks EVERYONE for your help.
It ended up being a matter of a space before the end of one of the lines.
Since there has to be line continuations (still not understanding as to why there has to be)...but, on a couple of the ends of these lines I missed a space before the " which messed up the query SQL.
After much line by line verification I found the problems, fixed them and came up with the final VB (which works):

Private Sub Macro1()

 EndCell = Sheets("DEATH").Range("$A$65536").End(xlUp).Row
 If EndCell = 6 Then
  incidentrange = Sheets("DEATH").Range("A6")
  Else
 Sheets("DEATH").Select
 ActiveWorkbook.Names.Add Name:="Incident", RefersToR1C1:= _
        "=R6C1:R" & EndCell & "C1"

incidentrange = joinit(Sheets("DEATH").Range("Incident"), ",")
MsgBox ("Incident Range = " & incidentrange), vbOKOnly
End If

Sheets("Query2").Select
        
With Selection.QueryTable
        .Connection = "ODBC;DSN=VIPER TP10;UID=adhoc;PWD=viper01;SERVER=vprprd.world;"
        .CommandText = Array( _
        "SELECT DISTINCT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, A_COMPL_SUMMARY.PART_SEQUENCE, INCIDENT_NOTIFICATION.INST_NAME, A_COMPL_SUMMARY" _
, _
        ".NAME, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.CASE_OWNER, A_COMPL_" _
, _
        "SUMMARY.NAME, A_COMPL_SUMMARY.PRODUCT_FAMILY, MDR_REPORTING.INCIDENT_NUMBER, " _
, _
        "MDR_REPORTING.CASE_NUMBER, MDR_REPORTING.PART_SEQUENCE, MDR_REPORTING.REPORT_I_" _
, _
        "F_NO, MDR_REPORTING.EVENT_DESC " _
        & "FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.INCIDENT_NOTIFICATION " _
, _
        "INCIDENT_NOTIFICATION, CHSUSER.MDR_REPORTING MDR_REPORTING " _
& "WHERE MDR_REPORTING.CASE_NUMBER = A_COMPL_SUMMARY.CASE_NUMBER AND MDR_REPORTING" _
, _
        ".INCIDENT_NUMBER = A_COMPL_SUMMARY.INCIDENT_NUMBER AND MDR_REPORTING.PART_" _
, _
        "SEQUENCE = A_COMPL_SUMMARY.PART_SEQUENCE AND INCIDENT_NOTIFICATION.INCIDENT_" _
, _
        "NUMBER = MDR_REPORTING.INCIDENT_NUMBER AND INCIDENT_NOTIFICATION.INCIDENT_NUMBER " _
, _
        "= A_COMPL_SUMMARY.INCIDENT_NUMBER AND ((A_COMPL_SUMMARY.INCIDENT_NUMBER " _
, _
        "In (" & incidentrange & ")))" _
& "ORDER BY A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE")

End With

Sheets("Query2").Range("C6").Select
Selection.QueryTable.Refresh BackgroundQuery:=True