# Conversion of column list to comma delimited string

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### rn119

##### New Member
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
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
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...

-------------------

#### rn119

##### New Member
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
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
1 - Every column is independent of each other.

2 - Nope.

#### Rick Rothstein

##### MrExcel MVP
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
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``````