Solved

# Looking to Combine Unique Values in Record into Cell with Commas

Posted on 2012-09-17
Medium Priority
386 Views
Looking for macro to go from individual records to combined records with unique values combined in cell with commas in a new tab.  See ID27862845 where it goes from combined records to individual records in a new tab.

Records Before

Records After
SamplePeanutButterProducts2009-U.xls
0
Question by:Alex Campbell

LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 38408084
Hi

Try this.

``````Option Explicit

Sub kTest()

Dim i   As Long, t, strConcat As String
Dim ka, k(), n As Long, c As Long

ka = Worksheets("Before").Range("a1").CurrentRegion.Value2
ReDim k(1 To UBound(ka, 1), 1 To UBound(ka, 2))

With CreateObject("scripting.dictionary")
.comparemode = 1

For i = 2 To UBound(ka, 1)
If Len(ka(i, 7)) Then 'unique UPC code
If Not .exists(ka(i, 7)) Then
n = n + 1
For c = 1 To UBound(ka, 2)
k(n, c) = ka(i, c)
Next
Else
t = .Item(ka(i, 7))
strConcat = k(t, 5) & "," & ka(i, 5)
k(t, 5) = strConcat
End If
End If
Next
End With
If n Then
With Worksheets("After")
.UsedRange.ClearContents
.Range("a1").Resize(, UBound(k, 2)) = Application.Index(ka, 1, 0)
.Range("a2").Resize(n, UBound(k, 2)) = k
End With
End If
End Sub
``````

Kris
0

LVL 1

Author Closing Comment

ID: 38409567
Worked great! Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question