Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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(xld own)) 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. -- Regards, Tom Ogilvy "Lance" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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 "Lance" wrote in message oups.com... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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. "Jim May" wrote in message news:wvr3f.5096$jw6.4508@lakeread02... 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 "Lance" wrote in message oups.com... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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 -- Regards, Tom Ogilvy "Lance" wrote in message oups.com... 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
It now runs with no errors, but doesn't do anything.
I'm off home, so will chat with you all tomorrow. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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. -- Regards, Tom Ogilvy "Lance" wrote in message ups.com... It now runs with no errors, but doesn't do anything. I'm off home, so will chat with you all tomorrow. Thanks |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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 ------------------- If Application.CountIf(rngO, rngHR(i)) = 0 Then rngHR(i, 1).Interior.ColorIndex = ------------------- Okay, end of my thanks : -- Lav ----------------------------------------------------------------------- Lava's Profile: http://www.excelforum.com/member.php...fo&userid=2779 View this thread: http://www.excelforum.com/showthread.php?threadid=47536 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Delete Duplicates across two Excel Worksheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro find and delete duplicates in a spread sheet. | Excel Programming | |||
Find Duplicates Across Two Sheets, Delete Everything else. | Excel Worksheet Functions | |||
Excel; how to delete duplicates in a long column of data?? | Excel Discussion (Misc queries) | |||
How do I find (not delete) duplicates in multiple spreadsheets? | Excel Worksheet Functions | |||
how do i find and delete duplicates in excel worksheet? | Excel Discussion (Misc queries) |