Finding Common Items

[KS1995]

Hi,

I'm having some trouble with comparing two sheets found in two different workbooks and highlighting any common items between them, I'm hoping there is an easy way to do this?

If the two sheets were in the same workbook I could probably work it out but because they're different files I'm completely stumped.

Thanks.

 

[bhos123]

1> you can move/copy a sheet from one file to other, and use formula = A1=sheet1!A1, resulting cells with TRUE in it will give you the desired results.

Or

2> you can still use the formula from different files but opened in same session.

Thanks,
Mahipal

 

[Oeldere]

why not add all data in 1 workbook?

Open both workbooks and create the link, in the same way you create a link between worksheets.

 

[MandeepBaluja]

Try this code ensure workbooks should open you have to assign it otherwise i need to add code to open workbook too.

    Option Base 1    
    Sub Check_common_Values()
    
    Dim Fdic As Object
    Dim Sdic As Object
    Dim Farray As Variant
    Dim Sarray As Variant
    Dim Carray() As Variant
    Dim uarray() As Variant
    Dim wb As Workbook
    Dim wb1 As Workbook
    
    
    Set Fdic = CreateObject("Scripting.dictionary")
    Set Sdic = CreateObject("Scripting.dictionary")
    Debug.Print Sheets("sheet1").UsedRange.Address
    Debug.Print Sheets("sheet2").UsedRange.Address
    
    Set wb = Workbooks("workbookname")
    Set wb1 = Workbooks("workbookname2")
    
    
    Farray = wb.Sheets("sheet1").UsedRange
    Sarray = wb1.Sheets("sheet1").UsedRange
    
    For i = LBound(Farray) To UBound(Farray)
        For j = LBound(Farray, 2) To UBound(Farray, 2)
                If Not Fdic.exists(Farray(i, j)) Then
                        Fdic.Add Farray(i, j), CStr(Farray(i, j))
    End If
    
    Next
    Next
    
    For i = LBound(Sarray) To UBound(Sarray)
        For j = LBound(Sarray, 2) To UBound(Sarray, 2)
                If Not Sdic.exists(Sarray(i, j)) Then
                        Sdic.Add Sarray(i, j), CStr(Sarray(i, j))
                End If
        Next
    Next
    
    
    F = 1
    For Each x In Fdic
            If Sdic.exists(x) Then
                ReDim Preserve Carray(F)
                    Carray(F) = x
                F = F + 1
            End If
    Next
    
    
    L = 1
    For Each x In Sdic
        If Not Fdic.exists(x) Then
                 ReDim Preserve uarray(L)
                    uarray(L) = x
                L = L + 1
        End If
    Next
    
    
    For Each x In Fdic
            If Not Sdic.exists(x) Then
                ReDim Preserve uarray(L)
                    uarray(L) = x
                L = L + 1
    End If
    Next
    
    Set wb3 = Workbooks.Add
    wb3.Sheets("sheet1").Range("A1:B1") = Array("Common", "unique")
    wb3.Sheets("sheet1").Range("A2").Resize(UBound(Carray), 1) = Application.Transpose(Carray)
    wb3.Sheets("sheet1").Range("B2").Resize(UBound(uarray), 1) = Application.Transpose(uarray)
    
    End Sub