Conversion of column list to comma delimited string

rn119

New Member
Joined
Feb 27, 2013
Messages
47
Actually I adjusted the macros to accommodate this guys. Thanks again.
 

rn119

New Member
Joined
Feb 27, 2013
Messages
47
Actually i have one more thing. In that same list, if USA were to occur twice, is there a way for either macro to eliminate duplicates?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,507
Office Version
2010
Platform
Windows
In that same list, if USA were to occur twice, is there a way for either macro to eliminate duplicates?
Assuming data starts in cell A1 and output is to go to cell B1, then give this a try...
Code:
Sub UniqueList()
  Dim X As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(Data)
      .Item(Data(X, 1)) = 1
    Next
    Range("B1").Value = Join(.Keys, ",")
  End With
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,255
Office Version
365
Platform
Windows
Or

a) If you have the TEXTJOIN function in your version of Excel
Code:
Sub Comma_List_v2()
  Dim addr As String
  
  addr = Range("A1", Range("A" & Rows.Count).End(xlUp)).Address
  Range("B1").Value = Evaluate(Replace("=TEXTJOIN("","",1,IF(MATCH(#,#,0)=ROW(#)-" & Range(addr).Row & " +1,#,""""))", "#", addr))
End Sub
b) Otherwise
Code:
Sub Comma_List_v3()
  Dim c As Range
  Dim s As String
  
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
  Next c
  Range("B1").Value = Replace(Mid(s, 2, Len(s) - 2), ",,", ",")
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,507
Office Version
2010
Platform
Windows
b) Otherwise
Rich (BB code):
Sub Comma_List_v3()
  Dim c As Range
  Dim s As String
  
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
  Next c
  Range("B1").Value = Replace(Mid(s, 2, Len(s) - 2), ",,", ",")
End Sub
I am pretty sure you know this already, but you can omit the third argument to the Mid function that I highlighted in red above. Unlike Excel's MID function, the third argument to VB's Mid function is optional and, when omitted, returns the remainder of the text automatically.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,255
Office Version
365
Platform
Windows
I am pretty sure you know this already, but you can omit the third argument to the Mid function that I highlighted in red above. Unlike Excel's MID function, the third argument to VB's Mid function is optional and, when omitted, returns the remainder of the text automatically.
Yes I do know that Rick but if I omit that argument here I would not eliminate the comma that is at the end of my string at that point. :)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,507
Office Version
2010
Platform
Windows
Yes I do know that Rick but if I omit that argument here I would not eliminate the comma that is at the end of my string at that point. :)
Ah, I see. I did not look carefully at all of your code and, given a quick look, thought you did the code line inside the loop like I do in this kind of code...

If InStr("," & s & ",", "," & c.Value & ",") = 0 Then s = s & "," & c.Value

which then would have only had a leading comma to remove.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,255
Office Version
365
Platform
Windows
Yeah, just a slightly different approach. Mine has a little extra manipulation to do once at the end, yours has a tiny bit more concatenation to do each time through the loop. No real difference either way as I see it. :)
 

rn119

New Member
Joined
Feb 27, 2013
Messages
47
Peter,

i tried your approach and it works somewhat. I'll give you more of a defined output here to work with.



USA100
Germany200
Chile300
France100
Turkey400
Indonesia500
USA300

<tbody>
</tbody>




I need the data to come out like this...


(USA,Germany,Chile,France,Turkey,Indonesia) in row C1
(100,200,300,400,500) in row C2




Currently the data is coming out like this...




(USA,Germany,Chile,France,Turkey,Indonesia) in row C1 (correct)
(USA,Germany,Chile,France,Turkey,Indonesia,100,200,300,400,500) in row C2 (incorrect)

I altered your base code to this.

Dim c As Range
Dim s As String

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
Range("C1").Value = "(" & Replace(Mid(s, 2, Len(s) - 2), ",,", ",") & ")"
Next c
For Each c In Range("B1", Range("B" & Rows.Count).End(xlUp))
If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
Next c
Range("C2").Value = "(" & Replace(Mid(s, 2, Len(s) - 2), ",,", ",") & ")"




Can you let me know what revisions I need to make this work?
 

Forum statistics

Threads
1,082,259
Messages
5,364,100
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top