How do i cross reference two excel spreadsheets?
 

I am using windows xp and want to cross reference two excel spreadsheets and
take out any duplicated data. How do i do this?

How do i cross reference two excel spreadsheets?
 

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?

How do i cross reference two excel spreadsheets?
 

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?

How do i cross reference two excel spreadsheets?
 

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?

How do i cross reference two excel spreadsheets?
 

Hi Paula and Martin,
You definitely don't want to spend 3 minutes per column checking each cell
Methods of limiting in a macro are to
restrict to used range
use of a selection
use of SpecialCells to limit to a type of cell (specialcells by definition are within used range)
use of INTERSECT with the used range which could be used in the macro
after if is working nicely, you would want to turn off calculation, and screen refreshing within macro.

Since there is a problem you might take a look at Chip Pearson's page on
duplicates -- that's where a lot of people are sent. (look for duplicate)
http://www.cpearson.com/excel/topic.htm

Back to information on restricting the range and improving performance see
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/slowresp.htm

For information on installing and using someone else's macro see
http://www.mvps.org/dmcritchie/excel....htm#havemacro
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin" wrote in message ...
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?

How do i cross reference two excel spreadsheets?
 

Martin

Thanks for your help its exactly what i wanted.

Thanks again
Paula

"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?

How do i cross reference two excel spreadsheets?
 

Martin or anyone else that can help,

I have the same situation as Paula did, however in my problem I have up to
13 worksheets in an individual workbook. I am no programmer and I am having
trouble editing that macro you previously responded with.

I basically need to be able to find and highlight duplicates across multiple
worksheets. I do not know if this part is even possible but I would also
like to know from what worksheet the duplicate is found in. All the numbers
I want to compare are in column A.

James

"Martin" wrote:

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?