TEXT STRING separated by commas

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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,", ")
Code:
Function krakur(rng As Range, delim As String) As String
   krakur = Join(WorksheetFunction.Transpose(rng.Value),delim)
End Function
 

danial

Board Regular
Joined
Apr 29, 2006
Messages
107
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,", ")
Code:
Function krakur(rng As Range, delim As String) As String
   krakur = Join(WorksheetFunction.Transpose(rng.Value),delim)
End Function
:p
Work...
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

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

Board Regular
Joined
Sep 11, 2007
Messages
186
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)

Code:
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

Active Member
Joined
Jan 31, 2006
Messages
438
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):
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top