Conversion of column list to comma delimited string

[rn119]

Is there a macro I can use that would convert this...

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]Chile[/TD]
[/TR]
[TR]
[TD]France[/TD]
[/TR]
[TR]
[TD]Turkey[/TD]
[/TR]
[TR]
[TD]Indonesia[/TD]
[/TR]
</tbody>[/TABLE]

to this....

USA,Germany,Chile,France,Turkey,Indonesia

The column will have always data in Column A and rows could vary in terms of data input.

 

[rn119]

Actually I adjusted the macros to accommodate this guys. Thanks again.

 

[Peter_SSs]

Actually I adjusted the macros to accommodate this guys. Thanks again.

Cheers, glad you could adapt it.

 

[rn119]

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]

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

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]

Or

a) If you have the TEXTJOIN function in your version of Excel

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

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]

b) Otherwise

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.

 

[Peter_SSs]

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]

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]

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]

Peter,

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



[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]USA[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Chile[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Turkey[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Indonesia[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]




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?