Vlookup to go down until the last cell with data and copy unmatched data

[azazafzal]

Hi All

I have a workbook which has data from two different source in Sheet1 and Sheet2 what I need here is vba code that will be assigned to a button in sheet3 which will do a vlookup in sheet2 cell B2 and drag is the formula until last row of column A (because the rows number will increase or decrease every month). After the look up formula it should copy all the name from column a (sheet2) which does not find any match in Sheet1 to sheet3 in column D. The data will be huge so if possible could you please use a function.


Thank you..!!

 

[Luke M]

I'm not sure about the formula, but this is roughly what you described.

[pre]

Sub CopyData()
Dim lRow As Long
Application.ScreenUpdating = True
With Worksheets("Sheet2")
'Find last row in col A
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'May need to tweak this formula. Currently looks at columns A:B of sheet1,
'and returned data from 2nd column
.Range("B2:B" & lRow).Formula = "=VLOOKUP(A2,Sheet1!A:B,2,FALSE)"

.Range("B2:B" & lRow).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0, -1).Copy _
Worksheets("Sheet3").Range("D1")
Application.CutCopyMode = False

End With
Application.ScreenUpdating = True

End Sub

[/pre]

 

[azazafzal]

Luke - Its works great...!! Thank you very much...!!

One more thing as the vlookup formula is being filled in all the cells it makes the calculation very slow. Is it possible to run this faster..by using some functions instead of vlookup formula...????

 

[Smallman]

azazafzal


A custom function will be much slower than regular XL formula. XL functions like VLookup which Luke has given you are going to destroy any custom function you can come up with for speed. Your problem is the size of your dataset not the tool you use to manipulate it.


It might make a slight difference not looking up the whole Columns (A&B and choosing a smaller range) but I suspect it is your heavy data that is the issue.


Take care


Smallman

 

[Luke M]

Ditto to what Smallman said. Another way to speed things up would be to have the lookup data be sorted. You could then remove the

FALSE

argument from the VLOOKUP, and you should see significant improved calculation times.


Also, VLOOKUPs are not usually calulation intensive formulas. Do you happen to have a lot of other formulas, specifically volatile ones (OFFSET, TODAY, NOW, etc) which are having to needlessly get recalculated a lot?