#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
.
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 |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
.
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 | |
Display Modes | |
|
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 |