compare two columns and copy matched data

[Suryaprakash]

I have two workbooks, TheList.xls and Thedata.xls. I wish to compare Column B value in first workbook with column B value in second and whereever they match, copy data from column O to P of second workbook to corresponding row of first workbook at the same location, i.e. column O to P. Both workbooks have only one sheet. I could not find a similar macro in search. Could somebody help? Thanks.

 

[svkroy]

I have two workbooks, TheList.xls and Thedata.xls. I wish to compare Column B value in first workbook with column B value in second and whereever they match, copy data from column O to P of second workbook to corresponding row of first workbook at the same location, i.e. column O to P. Both workbooks have only one sheet. I could not find a similar macro in search. Could somebody help? Thanks.

I think you can definitely take some clue from here............

http://www.mrexcel.com/forum/showthread.php?t=592367

 

[Suryaprakash]

Thanks for your suggestion. I had gone through the thread which you have suggested but could not adapt it for my use. I dont want to delete anything from my second workbook TheData.xls because that would be a permanant source of data. What I want is to copy the values from column O and P (in case the values of column B match) from this second workbook into my first workbook.

Could you or any member suggest how to do this?

 

[John Davis]

Thanks for your suggestion. I had gone through the thread which you have suggested but could not adapt it for my use. I dont want to delete anything from my second workbook TheData.xls because that would be a permanant source of data. What I want is to copy the values from column O and P (in case the values of column B match) from this second workbook into my first workbook.

Could you or any member suggest how to do this?

Not sure I understand, but maybe?

Sub Suryaprakash()
Dim lr As Long
Dim rcell As Range

Workbooks("TheList.xls").Sheets("Sheet1").Activate

lr = Cells(Rows.Count, 2).End(xlUp).Row

    With Range("O2:O" & lr)
    
        .Formula = "=VLOOKUP(B2,[TheData.xls]Sheet1!$B$2:$O$65536,14,false)"
        .Value = .Value
        .Replace What:="#N/A", Replacement:="", LookAt:=xlWhole
        
    End With
    
    For Each rcell In Range("O2:O" & lr)
    
        If rcell.Value <> "" Then
        
            rcell.Copy rcell(, 2)
            
        End If
        
    Next rcell
    

    
    
End Sub

 

[Suryaprakash]

Dear John

Thanks for the code. I tried this code on a small test data. There is just one little problem. It returned the value of column O correctly but in column P, the same value was inserted. Actually it should return column P value in column P of first workbook. Just a little bit of tweaking is required in the code. I hope I am able to convey what I mean to say. Just as column O value is returned, it should return column P value also.

Thanks

 

[John Davis]

Dear John

Thanks for the code. I tried this code on a small test data. There is just one little problem. It returned the value of column O correctly but in column P, the same value was inserted. Actually it should return column P value in column P of first workbook. Just a little bit of tweaking is required in the code. I hope I am able to convey what I mean to say. Just as column O value is returned, it should return column P value also.

Thanks

Maybe:

Sub Suryaprakash1()
Dim lr As Long
Dim rcell As Range

Workbooks("TheList.xls").Sheets("Sheet1").Activate

lr = Cells(Rows.Count, 2).End(xlUp).Row

    With Range("O2:O" & lr)
    
        .Formula = "=VLOOKUP(B2,[TheData.xls]Sheet1!$B$2:$P$65536,14,false)"
        .Value = .Value
        .Replace What:="#N/A", Replacement:="", LookAt:=xlWhole
        
    End With
    
    With Range("P2:P" & lr)
    
        .Formula = "=VLOOKUP(B2,[TheData.xls]Sheet1!$B$2:$P$65536,15,false)"
        .Value = .Value
        .Replace What:="#N/A", Replacement:="", LookAt:=xlWhole
        
    End With

    
    
End Sub

 

[Suryaprakash]

Now the code is working exceedingly well. Thank you very much.

 

[John Davis]

Now the code is working exceedingly well. Thank you very much.

You're welcome. I'm glad it works for you, and thanks for the feedback.