Comparing excel sheets

ALAPHRIDUS · #1 06-06-2017, 02:54 PM

I have two sheets in same workbook. In sheet 1, column 1 contains ID numbers, while sheet 2 contains ID numbers which can also be in sheet 1.
My problem is this: I want excel to go through is row in sheet1 and checked whether the ID number in each cell is also found in Sheet 2 and return with false or true.

I am an oldie novice. Somebody help me, pls.

Logit · #2 06-06-2017, 06:13 PM

.

Code:

Option Explicit

  Sub Dups()  'Compares data Sheet1 & Sheet2 ... Highlights dupes on Sheet2
        Dim Rng As Range, Dn As Range
        With Sheets("Sheet1")
            Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
        End With
        With CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
            For Each Dn In Rng
                .Item(Dn.Value & Dn.Offset(, 1).Value) = Empty
            Next
            With Sheets("Sheet2")
                Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
            End With
            For Each Dn In Rng
                If .exists(Dn.Value & Dn.Offset(, 1).Value) Then
                    'Dn.Resize(, 2).Interior.Color = vbYellow
                    Dn.Offset(0, 2).Value = "True"
                Else
                    Dn.Offset(0, 2).Value = "False"
                End If
            Next
        End With
    End Sub

ArviLaanemets · #3 06-06-2017, 10:36 PM

OK, in column A of Sheet1 you have ID's. Where in Sheet2 you want to search for same id's? In some specific column, in some range of columns, or in any column?

Anyway, there is a formula which covers all possible cases:
(assuming ID's on Sheet1 start from A2, insert the formula p.e. into cell B2 and copy it down)
If you search fo ID's in Sheet2 in range of columns A:C
=(COUNTIF(Sheet2!$A:$C;Sheet1!$A2)>0)
If you search fo ID's in Sheet2 in column C
=(COUNTIF(Sheet2!$C:$C;Sheet1!$A2)>0)

Or if you define the tables on both sheets (you have to replace table and column names in formula with your own):
in whole Table2
=(COUNTIF(Table2;Table1[@ID])>0)
in column Column2 of table Table2
=(COUNTIF(Table2[Column2];Table1[@ID])>0)
in columns Column1:Column3 of table Table2
=(COUNTIF(Table2[[Column1]:[Column3]];Table1[@ID])>0)

ALAPHRIDUS · #4 06-07-2017, 10:15 AM

ArviLaanemets

I am attaching the file ID_file.xl Would you please check because an error box is showing after I input the formulas as indicated in your post.

https://drive.google.com/file/d/0B_1...ew?usp=sharing

ArviLaanemets · #5 06-07-2017, 10:55 AM

I have semicolons as function parameter delimiter - replace them with commas in formula.

Sorry, but I can't look at you attached file currently - no Excel on my comp.

Logit · #6 06-07-2017, 01:31 PM

.

Code:

Option Explicit



  Sub Dups()  'Compares data Sheet1 & Sheet2 ... Highlights dupes on Sheet2
        Dim Rng As Range, Dn As Range
        With Sheets("Sheet2")
            Set Rng = .Range(.Range("C2"), .Range("C" & Rows.Count).End(xlUp))
        End With
        With CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
            For Each Dn In Rng
                .Item(Dn.Value & Dn.Offset(, 1).Value) = Empty
            Next
            With Sheets("Sheet1")
                Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
            End With
            For Each Dn In Rng
                If .exists(Dn.Value & Dn.Offset(, 1).Value) Then
                    'Dn.Resize(, 2).Interior.Color = vbYellow
                    Dn.Offset(0, 1).Value = "True"
                Else
                    Dn.Offset(0, 1).Value = "False"
                End If
            Next
        End With
    End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
new to VBA help comparing two sheets	mlttkw	Excel Programming	1	04-21-2016 08:32 AM
HELP - comparing spreadsheets in excel 2010	ali212006	Excel	1	04-03-2014 04:04 AM
Comparing two excel worksheets	SaneMan	Excel	1	06-27-2012 07:52 PM
Excel sheets	ubns	Excel	4	04-15-2012 10:48 PM
Comparing Data - MS Excel	ramadevidokkuud	Excel	1	05-19-2011 05:52 AM