Conversion of column list to comma delimited string

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,247
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
 

rn119

New Member
Joined
Feb 27, 2013
Messages
47
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
42,247
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,505
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
47
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,505
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
47
1 - Every column is independent of each other.

2 - Nope.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
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
42,247
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
 

Forum statistics

Threads
1,082,151
Messages
5,363,428
Members
400,736
Latest member
Aida

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