Excel - finding duplicate values over multiple columns-CodePudding

I have a data set containing phone numbers three different users. Each one is its own column in Excel. Eg Alice called the numbers in A:A, Bob called the numbers in B:B and Carol called the numbers in C:C (each column is a different length).

I am trying to determine which phone numbers all 3 have called. I have used unique to filter out duplicates from each list and I know how to find duplicate values over two columns using conditional formatting. But how do I find values that ONLY appear in all three columns?

Clarification: A mockup of the data is as follows (I can't give out the real data): | 1 | 5656 | 6464 | | | |-----|------|------|---|---| | 2 | 1 | 456 | | | | 456 | 2 | 2 | | | | 345 | 800 | 1 | | |

I want excel to look through the three columns and find any values which are common to all 3 columns and highlight them.

CodePudding user response：

Without additional information from the question in terms of data input and expected output. You can use the following assuming the phones numbers are in the range A2:C10:

IFERROR(HSTACK(UNIQUE(A2:10), UNIQUE(B2:B10), UNIQUE(C2:C10)),"")

CodePudding user response：

You will need to change the ranges, I used just 3 rows to test it.

Sub FindCommonNumbers()
    Dim dict As Object
    Dim rngAlice As Range, rngBob As Range, rngCarol As Range
    Dim cell As Range
    Dim phone As Variant
    ' Create a new dictionary object
    Set dict = CreateObject("Scripting.Dictionary")

    ' Set the range for each user's phone numbers
    Set rngAlice = Range("A1:A3")
    Set rngBob = Range("B1:B3")
    Set rngCarol = Range("C1:C3")

    ' Add the phone numbers from the first column to the dictionary
    For Each cell In rngAlice
        dict(cell.Value) = 1
    Next cell

    ' Check the phone numbers in the second column against the dictionary
    For Each cell In rngBob
        If dict.exists(cell.Value) Then
            dict(cell.Value) = dict(cell.Value)   1
        End If
    Next cell

    ' Check the phone numbers in the third column against the dictionary
    For Each cell In rngCarol
        If dict.exists(cell.Value) Then
            dict(cell.Value) = dict(cell.Value)   1
        End If
    Next cell

    ' Print the phone numbers that appear in all three columns
    For Each phone In dict.Keys()
        If dict(phone) = 3 Then
            Debug.Print phone
        End If
    Next phone
End Sub