Looking to Combine Unique Values in Record into Cell with Commas

Posted on 2012-09-17
Medium Priority
Last Modified: 2012-09-18
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
Peanut Records Before

Records After
Peanut Records AfterSamplePeanutButterProducts2009-U.xls
Question by:Alex Campbell
LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 38408084

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)
                    .Add ka(i, 7), n
                    t = .Item(ka(i, 7))
                    strConcat = k(t, 5) & "," & ka(i, 5)
                    k(t, 5) = strConcat
                End If
            End If
    End With
    If n Then
        With Worksheets("After")
            .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

Open in new window


Author Closing Comment

by:Alex Campbell
ID: 38409567
Worked great! Thanks

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question