Intersection of records in two spreadsheets
 

Please can someone advise?

I have two spreadsheets - in column A in both there is data listing
ACCOUNT_CODES. The other fields are all different. I need to find the
intersection (i.e. common records) between the two based on
ACCOUNT_CODES and list these in a third spreadsheet. There are about
30,000 records in each.

Many thanks - Chris B.

Intersection of records in two spreadsheets
 

Hi
have a look at
http://www.cpearson.com/excel/duplic...tractingCommon

--
Regards
Frank Kabel
Frankfurt, Germany


Chris Brady wrote:
Please can someone advise?

I have two spreadsheets - in column A in both there is data listing
ACCOUNT_CODES. The other fields are all different. I need to find the
intersection (i.e. common records) between the two based on
ACCOUNT_CODES and list these in a third spreadsheet. There are about
30,000 records in each.

Many thanks - Chris B.

Intersection of records in two spreadsheets
 

Chris
Here is a macro to do what you want. It works with sheets "First",
"Second", "Third" and copies Columns A:E to the "Third" sheet whenever
duplicate entries are found in Column A of the other two sheets. Modify
this as necessary.
I am also sending you a small file that has it all set up. Post back if
you don't get this file. HTH Otto
Sub FindDups()
Dim List1 As Range
Dim List2 As Range
Dim i As Range
Dim FoundCell As Range
With Sheets("First")
Set List1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("Second")
Set List2 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("Third")
For Each i In List1
On Error Resume Next
Set FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
On Error GoTo 0
If Not FoundCell Is Nothing Then
i.Resize(, 5).Copy .Range("A" & Rows.Count).End(xlUp)(2)
FoundCell.Resize(, 5).Copy .Range("A" &
Rows.Count).End(xlUp)(2)
End If
Next i
End With
End Sub

"Chris Brady" wrote in message
om...
Please can someone advise?

I have two spreadsheets - in column A in both there is data listing
ACCOUNT_CODES. The other fields are all different. I need to find the
intersection (i.e. common records) between the two based on
ACCOUNT_CODES and list these in a third spreadsheet. There are about
30,000 records in each.

Many thanks - Chris B.