Intersection of records in two spreadsheets

[Chris Brady]

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.

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon

 

[Otto Moehrbach]

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

 

[mubiru fahad]

otto how you doing...please help me out in excel....i also have 2 worksheets in excel but want to get the intersection of both and record it in sheet 3.....to add on that there is only one column in each of the sheets but having lots of numbers and cant do it manually...so i would want you to help me