Need to compare two spreadsheets/columns

[Noir]

I have two spreadsheets created by two people who were documenting equipment serial numbers within our company. Each person was supposed to track their own respective product but, we believe they tracked some of the same serial numbers.

How do i "quickly" determine if the same serial numbers are present within both spreadsheets? (Each spreadsheet contains approx. 600 serial numbers). I would like to show the similar serial numbers with a highlight or bold font if possible. Can this be done with Conditional Formatting?

Thx,
Noir

[Asala42]

Hey,

You could use a Vlookup from one sheet to verify any exact matches with the other sheet.

The excel help file is fairly useful in how it works. Basically if the vlookup cannot find a specific serial number on the other sheet, the formula will output the #N/A error. non-#N/A's would signify a match on the other sheet.

Hope that helps

Adam

[Noir]

Thanks Adam!

[Tom Urtis]

You can also try this, which assumes your sheets to be named Sheet1 and Sheet2, with the serial numbers in column A of each sheet and Sheet1's column B vacant. For all cells in column A of Sheet1 containing serial numbers also found in column A of Sheet2, this will shade those cells red, and bold the font. There is also an error trap in case there are no duplicates. The code looks a bit lengthy, due to an attempt to avoid looping for individual cell shading and bolding, in favor of a union range selection to format all duplicate cells at once. Modify for sheet names and column references:

Sub DuplicatesCheck()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range([A2], [A65536].End(xlUp)).Offset(0, 1).Formula = "=IF(COUNTIF(Sheet2!RC[-1]:Sheet2!R[598]C[-1],RC[-1])<>0,""Yes"",""No"")"
Dim theCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Set theCol = Range(Range("B2"), Range("B65536").End(xlUp))
LtoSel = "Yes"
For Each cell In theCol
If Right(cell, 3) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
On Error GoTo e
RtoSel.Offset(0, -1).Select
With Selection
.Font.FontStyle = "Bold"
.Interior.ColorIndex = 3
End With
Range([B2], [B65536].End(xlUp)).Clear
[A1].Select
Application.ScreenUpdating = True
Exit Sub
e:
MsgBox "There are no duplicates.", 64, "Time for a beer !"
[A1].Select
End Sub


HTH



_________________
Tom Urtis

[ This Message was edited by: Tom Urtis on 2002-04-05 03:41 ]

[Yogi Anand]

On 2002-04-04 12:30, Noir wrote:
I have two spreadsheets created by two people who were documenting equipment serial numbers within our company. Each person was supposed to track their own respective product but, we believe they tracked some of the same serial numbers.

How do i "quickly" determine if the same serial numbers are present within both spreadsheets? (Each spreadsheet contains approx. 600 serial numbers). I would like to show the similar serial numbers with a highlight or bold font if possible. Can this be done with Conditional Formatting?

Thx,
Noir

Hi Noir:
To expand upon what Adam suggested, you could combine the MATCH function with the VLOOKUP function to even locate the row number of the duplicate record -- as an example

in sheet1
abc
def
ghi
jkl
mno
pqr

in shee2
a12
abc
b12
jkl
c12
d12

then using the formula:
'=VLOOKUP(H16,Sheet2!$I$16:$I$21,1,FALSE)&" in row "&MATCH(H16,Sheet2!$I$16:$I$21,0)

you will get

abc in row 2
#N/A
#N/A
jkl in row 4
#N/A
#N/A