Conversion of column list to comma delimited string

[rn119]

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

USA

Germany

Chile

France

Turkey

Indonesia

<tbody>
</tbody>

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.

 

[Peter_SSs]

That is a somewhat different task now that some arithmetic is also involved, not just creating a list.
I would go back to a dictionary approach like Rick used earlier.

Sub CountryList()
  Dim i As Long
  Data As Variant
  
  Data = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(Data)
      .Item(Data(i, 1)) = .Item(Data(i, 1)) + Data(i, 2)
    Next i
    Range("C1").Value = Join(.Keys, ",")
    Range("C2").NumberFormat = "@"
    Range("C2").Value = Join(.Items, ",")
  End With
End Sub

 

[rn119]

Sorry Peter, column B actually doesn't have numeric data.....I just used it as an example....that column would have specific alphanumeric characters actually which should be treated as text.

 

[Peter_SSs]

Sorry Peter, column B actually doesn't have numeric data.....I just used it as an example....that column would have specific alphanumeric characters actually which should be treated as text.

In that case could you please give us some realistic sample data and expected results. If we go on guessing what you have and want, or working with unrepresentative data, we will keep delivering suggestions that are likely to fail whan you get to the actual data.

 

[Rick Rothstein]

In that case could you please give us some realistic sample data and expected results. If we go on guessing what you have and want, or working with unrepresentative data, we will keep delivering suggestions that are likely to fail whan you get to the actual data.

To follow up on what Peter posted above...

Please Note
-------------------
For questions you ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

 

[rn119]

Thanks for the feedback Peter/Rick. And apologies for the earlier sample data. Here is an actual snippet of data that I'm looking to manipulate.

USA	12YK010 <tbody> </tbody>	Seattle <tbody> </tbody>
Germany	55GZUV1 <tbody> </tbody>	Munich <tbody> </tbody>
Chile	90JW671 <tbody> </tbody>	Santiago
France	88TC190 <tbody> </tbody>	Paris
Turkey	73QW100 <tbody> </tbody>	Istanbul
Indonesia	50VC275 <tbody> </tbody>	Jakarta <tbody> </tbody>
USA	12YK010 <tbody> </tbody> <tbody> </tbody>	Houston
Tanzania	25PO569 <tbody> </tbody>	Dar Es Salaam
Japan	07CG370 <tbody> </tbody>	Tokyo
Brazil	88KP268 <tbody> </tbody>	Rio De Janiero <tbody> </tbody>

<tbody>
</tbody>

These columns represent A/B/C respectively. In Column D1, I need...

(USA, Germany, Chile, France, Turkey, Indonesia, Tanzania, Japan, Brazil)

In column D2, I need.....

(12YK010,55GZUV1,90JW671,88TC190,73QW100,50VC275,25PO569,07CG370,88KP268)

Finally, in column D3, I need...

(Seattle,Munich,Santiago,Paris,Istanbul,Jakarta,Houston,Dar es Salaam,Tokyo,Rio De Janiero)

Let me know if you guys have additional questions.

 

[Rick Rothstein]

These columns represent A/B/C respectively. In Column D1, I need...

(USA, Germany, Chile, France, Turkey, Indonesia, Tanzania, Japan, Brazil)

In column D2, I need.....

(12YK010,55GZUV1,90JW671,88TC190,73QW100,50VC275,25PO569,07CG370,88KP268)

Finally, in column D3, I need...

(Seattle,Munich,Santiago,Paris,Istanbul,Jakarta,Houston,Dar es Salaam,Tokyo,Rio De Janiero)

Questions...

1) Are the list formed from each column independent of each other? Or does the values in the list formed from Column B have to remain in the same order as the values in the list formed from Column A?

2) When there is a duplicate in Column A, will the Column B values adjacent to them always be the same (as your example shows for USA)?

 

[rn119]

1 - Every column is independent of each other.

2 - Nope.

 

[Rick Rothstein]

1 - Every column is independent of each other.

2 - Nope.

Here is my code modified to handle your actual data layout...

Sub UniqueList()
  Dim C As Long, X As Long, Data As Variant
  With CreateObject("Scripting.Dictionary")
    For C = 1 To 3
      Data = Range(Cells(1, C), Cells(Rows.Count, C).End(xlUp)).Value
        For X = 1 To UBound(Data)
          If Len(Data(X, 1)) Then .Item(Data(X, 1)) = 1
        Next
        Cells(C, "D").Value = Join(.Keys, ",")
        .RemoveAll
    Next
  End With
End Sub

 

[Peter_SSs]

If you wanted to stick with the string approach, try

Sub Comma_List_v4()
  Dim a As Variant
  Dim s As String
  Dim i As Long, j As Long
  
  a = Range("A1:C" & Range("A:C").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Value
  For j = 1 To UBound(a, 2)
    s = ",#"
    For i = 1 To UBound(a)
      If InStr(s, "," & a(i, j) & "#") = 0 Then s = s & "," & a(i, j) & "#"
    Next i
    Cells(j, 4).Value = Split(Replace(s, "#", ""), ",", 3)(2)
  Next j
End Sub