RE: How do i cross reference two excel spreadsheets?
- From: Martin <Martin@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 30 Mar 2006 06:30:01 -0800
Sorry - I forgot we're in the Worksheet Functions page, not Programming!
First up, if you haven't used macros before, do be very careful and make
backup copies of the data (with File, Save As) before doing anything like
this.
Also, before going ahead with my code, is it definitely two worksheets
you're comparing, not two workbooks?
If it's two sheets in the same book then this macro should be OK though you
may need to change the column number if you're not just comparing everything
in column A in both sheets (I'll go through that in a minute)
To make the macro work, go to Tools, Macro, Visual Basic Editor while in the
workbook we're talking about. On the left of this new window you should see
the name of your workbook in brackets after VBA Project (if not, click on
View, Project Explorer). Now click on Insert, Module and a blank window
should appear on the right called Module1 - copy and paste my macro into it
(everything from the Sub row to the End Sub row inclusive).
If the lists you're comparing are each in column A you don't need to change
the text at all but if the first sheet has its data in column B you'll need
to change:
For Each cell1 In sht1.Columns(1).Cells
to:
For Each cell1 In sht1.Columns(2).Cells
so it looks at the second column (B) instead - and so on.
Same for the data in the second sheet, in which case the line:
For Each cell2 In sht2.Columns(1).Cells
needs to be changed to:
For Each cell2 In sht2.Columns(2).Cells
To run the macro, move back to Excel (on the Task Bar at the bottom) and
choose Tools, Macro, Macros. You should see the macro's name on the list
(FindDupes); double-click to run it.
This macro takes a while - I'd have put something in to stop it (perhaps at
a blank cell) but I didn't know how your data is arranged. If you get bored,
press Esc or Ctrl-Break and then click on End; this might be a good idea when
you first run it, just to check it's working but you'll need to let it run to
be sure it's looked at every cell.
Good luck and let me know how it goes...
"Paula" wrote:
Thanks for your help but i'll be completely honest and my knowledge is not.
good enough to really understand. Is there any chance you could simplify it
for me. Sorry to be a pain
"Martin" wrote:
Manually, there's a great new feature (and I don't work for M/soft!):
Windows, Compare Side by Side which you use with both files open. Sort both
and then you can compare line by line, scrolling in sync.
Using macros, the following is the kind of thing. My code assumes the
sheets being compared are in the same book and that all data is typed in the
first column - you could obviously adapt it as necessary. It colours red all
dupes. Beware though, it currently goes from cell A1 all the way to be the
bottom of the sheet so you may want to Esc or Ctrl-Break to stop the macro
before then!
Sub FindDupes() 'assuming both sheets are in same book and book is open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
For Each cell1 In sht1.Columns(1).Cells
For Each cell2 In sht2.Columns(1).Cells
If cell2.Value = cell1.Value Then
cell1.Interior.ColorIndex = 3
cell2.Interior.ColorIndex = 3
End If
Next cell2
Next cell1
End Sub
"Paula" wrote:
I am using windows xp and want to cross reference two excel spreadsheets and
take out any duplicated data. How do i do this?
- Follow-Ups:
- Re: How do i cross reference two excel spreadsheets?
- From: David McRitchie
- Re: How do i cross reference two excel spreadsheets?
- Prev by Date: Re: validation and further
- Next by Date: Re: How do I make all columns in a range in a worksheet the same width
- Previous by thread: Re: Identifying Duplicates in Columns
- Next by thread: Re: How do i cross reference two excel spreadsheets?
- Index(es):
Relevant Pages
|
