CODEWITHSUNDEEP
vbaexcel
Crays
Crays

Reputation: 2508

VBA Excel Single out Duplicates in a single row of multiple rows

I have a table of data as such

A - B - A - A - B - C - B
E - E - E - D - D - E - F
G - G - H - H - H - I - H
J - K - K - K - K - L - L

is there a way for me to single out the A and B of the first row , E & D of the second row, basically the duplicates of EACH row, it has 2 diff duplicates in the same row.

Upvotes: 0

Views: 60

Answers (1)

NeepNeepNeep
NeepNeepNeep

Reputation: 913

Yes, you can use Dictionaries to achieve this. Go to the VB editor, then Tools, References and tick "Microsoft Scripting Runtime" to import the Dictionary functionality (this approach facilitates Intellisense).

This code is written to provide that functionality in the form of a worksheet function.

You can modify it to your needs - reply if you need help with doing that. I'm not sure how your data is formatted, hence the line to remove hyphens and spaces.

    Public Function ShowDuplicates(rngSource As Range) As String
    Dim dctUnique   As Dictionary
    Dim dctDups     Dictionary
    Dim intCounter  As Integer
    Dim strSource   As String
    Dim strCurrent  As String

    Set dctUnique = New Dictionary
    Set dctDups = New Dictionary

    ' Remove hyphen and space
    strSource = Replace(Replace(rngSource.Value, "-", ""), " ", "")

    For intCounter = 1 To Len(strSource)
        strCurrent = Mid$(strSource, intCounter, 1)
        If dctUnique.Exists(strCurrent) Then
            If Not dctDups.Exists(strCurrent) Then
                ' Only add it to the dups dict if required
                dctDups.Add strCurrent, strCurrent
            End If
        Else
            dctUnique.Add strCurrent, strCurrent
        End If
    Next

    If dctDups.Count > 0 Then
        ShowDuplicates = Join(dctDups.Keys(), ",")
    Else
        ShowDuplicates = ""
    End If

    End Function

Upvotes: 1

Related Questions