How do I match records from two Sheets?

[sebastico]

Excel 2003

Sheet1 has one column with 904 records (alphanumeric)
Sheet2 has one column with 1069 records (alphanumeric)
Both fields have same format (17 characters)

I need a code to read each record in sheet 1, then go to sheet 2 and read
the records, if the record is found then copy the record from sheet2 and
paste it in sheet1 next to the record. Do the same with all records in sheet2.

 

[Roger Govier]

Hi

On sheet1, enter the following formula in B1 and copy down
=IF(COUNTIF(Sheet2!A:A,A1),A1,"")

 

[JLatham]

This code should do it for you. You'll need to edit it to enter the correct
sheet names, column IDs that the records are in on each sheet, and indicate
the row on each sheet where the records are.
To put it into your workbook, press [Alt]+[F11] to enter the VB Editor.
Choose Insert --> Module then copy the code and paste it into the code module
and then edit as needed. Run it from Tools --> Macro --> Macros (pre-Excel
2003) or from the [Developer] tab (2007)

Sub CopyMissingEntries()
'change these Const values
'as required
'name of sheet with short list
Const sheet1Name = "Sheet1"
'column short list is in
Const shortListCol = "A"
'first row with list data
Const firstShortRow = 2
'name of sheet with long list
Const sheet2Name = "Sheet2"
'column long list is in
Const longListCol = "A"
'first row with list data
Const firstLongRow = 2

Dim shortSheet As Worksheet
Dim shortList As Range
Dim anySLEntry As Range
Dim longSheet As Worksheet
Dim longList As Range
Dim anyLLEntry As Range
Dim foundFlag As Boolean

Set shortSheet = Worksheets(sheet1Name)
Set shortList = shortSheet.Range(shortListCol & _
firstShortRow & ":" & _
shortSheet.Range(shortListCol & Rows.Count). _
End(xlUp).Address)
Set longSheet = Worksheets(sheet2Name)
Set longList = longSheet.Range(longListCol & _
firstLongRow & ":" & _
longSheet.Range(longListCol & Rows.Count). _
End(xlUp).Address)
'to improve performance
Application.ScreenUpdating = False
'do the real work
For Each anyLLEntry In longList
foundFlag = False
For Each anySLEntry In shortList
If anySLEntry = anyLLEntry Then
foundFlag = True
Exit For ' out of inner loop
End If
Next
If Not foundFlag Then
'add to short list
shortSheet.Range(shortListCol & _
Rows.Count).End(xlUp).Offset(1, 0) = anyLLEntry
'get new short list range
Set shortList = shortSheet.Range(shortListCol & _
firstShortRow & ":" & _
shortSheet.Range(shortListCol & Rows.Count). _
End(xlUp).Address)
End If
Next
'housekeeping
Set shortList = Nothing
Set shortSheet = Nothing
Set longList = Nothing
Set longSheet = Nothing
'announce completion
MsgBox "Task Completed"
End Sub

 

[JLatham]

I may have misunderstood your request. The code above will find entries on
sheet 2 that are MISSING from sheet 1 and add them to the list on sheet 1.

You seem to want something that matches the records and brings over data to
sheet 1 that doesn't yet exist.

You can do that with a VLOOKUP formula rather than with code. Assuming your
records on both sheets go from column A over to column G and the codes are in
column A on both sheets, then beginning at row 2 (column B) you could enter
this formula:
=VLOOKUP($A2,'Sheet2'!$A$2:$G$1070,2,FALSE)
and that will bring up matching data from column 2 of Sheet2 to Sheet1, then
in
Sheet1, C2 enter this:
=VLOOKUP($A2,'Sheet2'!$A$2:$G$1070,3,FALSE)
as you can see, it's the same formula, just changing the ,2, to ,3, Repeat
the copying of the formula across to column G (or where ever your records
end) changing the ,2, (or ,3,) to the next increasing value to get different
column information from Sheet2. Then copy the formulas down the sheet to the
end of the data on Sheet1.

 

[sebastico]

JLathan and Roger

Finally I was able to use your code (both) and works well.
Many thanks