How to match records from two Sheets in Microsoft Excel

[Shaina Na]

Hi friends,

I have two sheet, Sheet1 has one column with 904 records (alphanumeric) and 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. So I just want to match records from two Sheets in Microsoft Excel. If you have any ideas then please let me know.

[Hollyn]

According to my suggestion you can try this on sheet1, enter the following formula in B1 and copy
=IF(COUNTIF(Sheet2!A:A,A1),A1,"")

[Koman]

You can just use the below code.

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

[Shurod]

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 to use the code. Assuming your records on both sheets go from column A over to column B 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.