Conversion of column list to comma delimited string

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,684
Office Version
365
Platform
Windows
That is a somewhat different task now that some arithmetic is also involved, not just creating a list. :eek:
I would go back to a dictionary approach like Rick used earlier.

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

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

rn119

New Member
Joined
Feb 27, 2013
Messages
49
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,684
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,923
Office Version
2010
Platform
Windows
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

New Member
Joined
Feb 27, 2013
Messages
49
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,923
Office Version
2010
Platform
Windows
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

New Member
Joined
Feb 27, 2013
Messages
49
1 - Every column is independent of each other.

2 - Nope.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,923
Office Version
2010
Platform
Windows
1 - Every column is independent of each other.

2 - Nope.
Here is my code modified to handle your actual data layout...
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,684
Office Version
365
Platform
Windows
If you wanted to stick with the string approach, try
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,324
Messages
5,467,943
Members
406,560
Latest member
dwinsburg

This Week's Hot Topics

Top