Peter,
i tried your approach and it works somewhat. I'll give you more of a defined output here to work with.
USA | 100 |
Germany | 200 |
Chile | 300 |
France | 100 |
Turkey | 400 |
Indonesia | 500 |
USA | 300 |
<tbody>
</tbody>
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?