Find & Delete Duplicates across two Excel Worksheets

  • Thread starter Thread starter Lance
  • Start date Start date
L

Lance

Hi Everyone,

I've done my research on other Google groups and have attempted other
VBA codes, but have been unlucky and unable to do this task. Hope you
all can help.

Workbook Structure

I have 2 worksheets in the workbook, called 'HR' & 'Oracle'.
Both sheets contain Unique Values for that sheet in Column A.
The unique values are NOT orders the same on both sheets and can appear
anywhere between A1 and A5000 on either sheets.
The sheet 'Oracle' has up-to-date information
The sheet 'HR' is the least up-to-date

(Still with me? Good)

Required Action

I need to check the values from 'Oracle' Colmun A against the values
from 'HR' Colmun A. If the system finds values in 'HR' that are in
'Oracle', then it should delete the whole Row in 'HR'. Remember that
the Values are not ordered in any way and can appear anywhere in Column
A on both sheets.

Sample of 'HR' worksheet

A B
0000049821 KELLY M
0000053167 LEACH D
0000054090 LEACH G
0000061784 MILLING G
0000061220 MILLS S
0000069255 MULHERN N
0000063288 MCNEILL P
0000067363 NELSON F

Thank you all for your help & time,

Lance
 
you may want to have a for each loop
a look in vba help index for FIND would have given you this

Sub findinlist()
For Each c In [lista]
MsgBox [listb].Find(c).Address
Next c
End Sub
Sub findallinlist()
For Each x In [lista]
With [listb]
Set c = .Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next x
End Sub
 
Assumes the first row in each sheet has header information.

Sub CompareData()
Dim shHR as Worksheet, shOracle as Worksheet
Dim rngHR as Range, rngO as Range
Dim i as Long
set shHR = Workbooks("HR.xls").Worksheets(1)
set shOracle = Workbooks("Oracle.xls").Worksheets(1)
set rngHR = shHR.Range(shHR.Cells(2,1),shHR.Cells(2,1).End(xldown))
set rngO =
shOracle.Range(shOracle.Cells(2,1),shOracle.Cells(2,1).End(xldown))
for i = rngHR.count to 1 step -1
if application.Countif(rngO,rngHR(i)) > 0 then
rngHR(i).EntireRow.Delete
end if
Next
End Sub

Testing should be done on a copy of your data.
 
Thanks Don & Tom, but neither seem to work first time.

I've tried the VBA code from Tom as it looks more suitable for my
needs.
However when running the code I get a Compile Error : Error Syntax on
line 'set rngO = '
Am i supposed to run this code from a certain spreadsheet? Do I need
to select anything first? Or can it just be executed anywhere within
the workbook?

Any more ideas?
 
set rngO =
shOracle.Range(shOracle.Cells(2,1),shOracle.Cells(2,1).End(xldown))

The above should be ON ONE LINE, not two -- Fix to:

set rngO =
shOracle.Range(shOracle.Cells(2,1),shOracle.Cells(2,1).End(xldown))

HTH
 
Crap, computers.... GRRRR
When I sent the above my last line was all in one row,
yet it appears in two rows erroneously.. Hopefully, you get the idea.
 
I did actually think of that before and try it. Should give myself a
little more credit.

On running the vba code as you mentioned i now receive this error.

Run Time Error '9':
Subscript out of range

Debug highlights this row
Set shHR = Workbooks("HR.xls").Worksheets(1)

I'm no VBA / Marco expert, but it looks like the vba code is looking
for 2 workbooks. Whereby i only have one workbook and 2 worksheets
within the workbook.

Next idea?
 
I did actually think of that before and try it. Should give myself a
little more credit.

On running the vba code as you mentioned i now receive this error.

Run Time Error '9':
Subscript out of range

Debug highlights this row
Set shHR = Workbooks("HR.xls").Worksheets(1)

I'm no VBA / Marco expert, but it looks like the vba code is looking
for 2 workbooks. Whereby i only have one workbook and 2 worksheets
within the workbook.

Next idea?

p.s - Thanks again for all the help
 
Sorry, I read it as two workbooks

Sub CompareData()
Dim shHR as Worksheet, shOracle as Worksheet
Dim rngHR as Range, rngO as Range
Dim i as Long
set shHR = Worksheets("HR")
set shOracle = Worksheets("Oracle")
set rngHR = _
shHR.Range(shHR.Cells(2,1), _
shHR.Cells(2,1).End(xldown))
set rngO = _
shOracle.Range(shOracle.Cells(2,1), _
shOracle.Cells(2,1).End(xldown))
for i = rngHR.count to 1 step -1
if application.Countif(rngO,rngHR(i)) > 0 then
rngHR(i).EntireRow.Delete
end if
Next
End Sub
 
It now runs with no errors, but doesn't do anything.
I'm off home, so will chat with you all tomorrow.

Thanks
 
In contrast, I tested it and it worked OK for me, so I believe the
code/approach is OK. Perhaps the problem is in your data. Maybe you have
leading or trailing blanks or something.
 
Thank you, thank you for your indirect help :)

I had a situation where I had to use a reference list to check th
validity of an inserted value. It was done before by means o
conditional formatting having a reference list on the same worksheet a
where the to-be-checked list was. No match meant the cell had to b
coloured red.

Your code was absolutely clear to understand and with som
modifications I made it work not to delete something on a value found
but to label something on no value found:

Code
 
Tom, You are brilliant!

Thank you. I tested it with a newly created workbook and it worked
perfectly. You were correct in assuming that the leading blanks were
causing the problem, (sorry for not mentioning it before).

If you live in the UK, I owe you a drink a two. Otherwise if I can help
in any other way, IT networks, Systems, PSP, just let me know.

Thank you very much and to everyone else that contributed.

Cheers,

Lance
 
Back
Top