Comparing Strings and Combining Duplicates as List

FierceMouse · Aug 13th 2021

Hello!

How should I go about getting VBA to look through an array, see that a repeat is in there and then have it combine the repeats into a list?

If ... value in cell is not unique in the column (sans header of course)... then store values to the left to then be joined with a delimiter in another column.

Visually:

I don't need to preserve colors, column widths, or any other formatting. I thought I'd use household products as the data I am actually using is abstract and may not get the idea across as well. Here is the workbook I took the above screenshot from: Source and Product.xlsx

Is this task going to be best approached through making a dictionary or a collection? Or something else? I haven't used either before. I'm just getting ok with loops and arrays in general.

Also, the order in which the sources appear do not necessarily need to be maintained either.

Thanks in advance!

**alansidman** · Aug 14th 2021

Cannot manipulate data in a picture Please upload your sample workbook. This can be achieved in Power Pivot but reproducing your data to fix your problem is time consuming and you already have it.

**pike** · Aug 14th 2021

Hello,

One way

Code

Option Explicit
Sub testrev()
    Dim a, b(), i!, n!, temp As String, e
    With Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 2)
        a = .Value
    End With
    ReDim b(1 To UBound(a, 1), 1 To 2)
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a, 1)
            If Not IsEmpty(a(i, 2)) Then
                If Not .exists(a(i, 2)) Then
                    n = n + 1
                    b(n, 1) = a(i, 2)
                    .Add a(i, 2), n
                End If
                b(.Item(a(i, 2)), 2) = b(.Item(a(i, 2)), 2) & IIf(b(.Item(a(i, 2)), 2) <> "", ",", "") & " " & a(i, 1)
            End If
        Next
        .RemoveAll
        For i = 1 To n
            For Each e In Split(b(i, 2), ",")
                If Not .exists(e) Then
                    temp = temp & "," & e
                    .Add e, Nothing
                End If
            Next
            b(i, 2) = Mid$(temp, 2)
            temp = vbNullString
            .RemoveAll
        Next
    End With
    Range("D3").Resize(n, 2).Value = b
End Sub

Display More

FierceMouse · Aug 16th 2021

One awesome way! Thanks so much, Pike. I was able to get it to work in my other workbook too despite all it's oddities. I super appreciate the time you spent on this. Have a stellar day.

-Mouse

FierceMouse · Aug 16th 2021

PS I appreciate these developed answers too more than just copying and pasting them. I try to follow where they are referencing and it's great to have real-to-me examples that now I can google things like "Scripting.Dictionary", .CompareMode, IIf, and why the heck this "Mid$" has a dollar sign in it. So, thank you for the opportunity to learn at a deeper level.

**pike** · Aug 17th 2021

yes always learning. it will be interesting to see Alan’s power pivot solution when he notices your attached workbook

**alansidman** · Aug 17th 2021

pike

thanks for pointing out that the file was attached. In my attachment, I have recreated the expected results using Power Pivot. I created a measure using the concatenatex function. The measure is

=CONCATENATEX(Table1,Table1[Source],", ")

**pike** · Aug 17th 2021

Hi alan,

where does

=CONCATENATEX(Table1,Table1[Source],", ")

go?

**alansidman** · Aug 18th 2021

pike

Here is a link to a article on how this works. I found this about a year ago and have used it many many times. This will be easier to follow than for me to regurgitate.

Alan

https://sfmagazine.com/post-en…ng-text-in-a-pivot-table/

Comparing Strings and Combining Duplicates as List

Participate now!

Share