Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-06-2017, 02:54 PM
ALAPHRIDUS ALAPHRIDUS is offline Comparing excel sheets Windows 10 Comparing excel sheets Office 2010 32bit
Novice
Comparing excel sheets
 
Join Date: Jun 2017
Location: MALTA - EUROPE
Posts: 2
ALAPHRIDUS is on a distinguished road
Default Comparing excel sheets

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.





Reply With Quote
  #2  
Old 06-06-2017, 06:13 PM
Logit Logit is offline Comparing excel sheets Windows 10 Comparing excel sheets Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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
Attached Files
File Type: xlsm Dupes Find and Mark 2 Sheets.xlsm (18.7 KB, 11 views)
Reply With Quote
  #3  
Old 06-06-2017, 10:36 PM
ArviLaanemets ArviLaanemets is offline Comparing excel sheets Windows 8 Comparing excel sheets Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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)
Reply With Quote
  #4  
Old 06-07-2017, 10:15 AM
ALAPHRIDUS ALAPHRIDUS is offline Comparing excel sheets Windows 10 Comparing excel sheets Office 2010 32bit
Novice
Comparing excel sheets
 
Join Date: Jun 2017
Location: MALTA - EUROPE
Posts: 2
ALAPHRIDUS is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 06-07-2017, 10:55 AM
ArviLaanemets ArviLaanemets is offline Comparing excel sheets Windows 8 Comparing excel sheets Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #6  
Old 06-07-2017, 01:31 PM
Logit Logit is offline Comparing excel sheets Windows 10 Comparing excel sheets Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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
Attached Files
File Type: xlsm ID_Files.xlsm (24.1 KB, 7 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing excel sheets 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
Comparing excel sheets Excel sheets ubns Excel 4 04-15-2012 10:48 PM
Comparing Data - MS Excel ramadevidokkuud Excel 1 05-19-2011 05:52 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:01 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft