Need to compare two spreadsheets/columns

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top