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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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