FIND MATCHING ROWS OF DATA IN TWO EXCEL WORKSHEETS. IF A MATCH IS FOUND, PASTE DATA FROM 2ND SHEET INTO 1ST

I have two Excel spreadsheets (Sheet1 and Sheet2). Each sheet has several thousand rows of data. The first column in both sheets have unique data (MemberId) that may/may not match. Sheet2, has a NOTES column that may/may not have data.  I need VBA code that will search MemberId in both sheets to find all matches. If/when a MemberId match is found, copy and paste the NOTES value in Sheet2 into a corresponding NOTES column in Sheet1. I am familiar with VBA code, but somewhat rusty...Thanks in advance!
waverobberAsked:
Who is Participating?
 
KoenChange and Transition ManagerCommented:
Why VBA, add a formula to your sheet 1 comment field and lookup the comment from sheet2 based on the  MemberId

something like this (in your comment columns on sheet1, row 2):  =iferror(vlookup(a2,sheet2!$A$1:$R$9999,5,0),"")
this will lookup in sheet2, to find a match with cell a2 and when it finds a match copy over the 5th column.
you could use tables or named references instead of the absolute $a$1 to make sure it keeps working when adding/changing records
0
 
waverobberAuthor Commented:
Thanks for responding Koen. Why VBA? I'm a closet programmer by choice. Although I tested your recommend, I ended up figuring the VBA code by myself. Just had to put on the hat with dual propellers and dust off the VBA cobwebs. I do appreciate your input and give you full credit for pushing my cobwebs out of mind...Here's my VBA code:

 Sub CheckMatch()
   
    lastRowSheet1 = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    lastRowSheet2 = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Variant, j As Variant
       
    For i = 1 To (lastRowSheet1)
        For j = 1 To (lastRowSheet2)
            If Worksheets("Sheet1").Cells(i, 1) = Worksheets("Sheet2").Cells(j, 1) Then
                Worksheets("Sheet1").Cells(i, 8) = Worksheets("Sheet2").Cells(j, 6)
                Worksheets("Sheet1").Cells(i, 9) = Worksheets("Sheet2").Cells(j, 7)
             End If
         Next j
    Next i

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.