Sum with lookup

[Blunder1]

Hi All,

I have two worksheets which which contain around 20,000 rows of data in each. The important columns are A, B & C. What i need to do is start on sheets2 cell A2 (the reference), then look this up in sheet 1. If it finds it then sum the value of cells in sheets2 cell b2 with sheets1 column b, row that contain the reference. The sum needs to be in sheets2 columnAB. I need to then repeat this but rather sum the values from colmn B it'll be columns c and the sum in sheets2 column AC.

In my current code i use a vlookup and/or sumif's but they're slow, i have dug around the web site and scripting dirctories seem to be a quick way to do this, but they are out my VBA knowledge to adjust to suit!

Any help will be appreciated

Thanks in advance

Blunder

 

[DauntingGecko]

You can always try index and match formula's to try and speed things up. You have got a large amount of data (20,000+ rows) so it won't be as fast as normal.

Alternatively you can try and re-arrange your data in a better way so its easier to work with...

Do all 20,000 rows need to be calculated? Or can you take a sample of data?

 

[Mike Blackman]

Can we see your code for the Lookup?

 

[Blunder1]

as below. Thank you, Mikey

Range("Ac2:" & "ac" & lastrow).FormulaR1C1 = "=rc[-27]-vlookup(rc[-28],'Sheet1'!c[-28]:c[-27],2,0)"

Both sheets are sorted by reference, though not all the refs in Sheets 2 are in Sheet 1

Thanks

Blunder

 

[Mike Blackman]

Are the values you are looking up likely to change during the use of the workbook or will they be the same? If not you could just calculate them the once instead of embedding live formula.

Range("AC2:" & "AC" & LastRow).Value = Range("C2") - Application.VLookup(Range("B2:B" & LastRow), Sheets("ODS New").Range("A:B"), 2, 0)

Please check I have translated the RC addresses correctly, I don't use them

 

[Jon von der Heyden]

Hi

Using exact match VLOOKUPs are of the most common reasons for slow recalculation. Consider instead using VLOOKUPs approximate match method (i.e. last argument should be TRUE/1). This method uses a binary search and is considerably more efficient. COUNTIF is also a very efficient function to use so you can first test whether or not the lookup value exists in the lookup table. If it doesn't then return 0, or a null string, or even #N/A.

Formula in A1 notation:
=IF(COUNTIF(Sheet1!A:A,A2),B2-VLOOKUP(A2,Sheet1!A:B,2,1),0)

And in R1C1 notation:
=IF(COUNTIF(Sheet1!C[-28],RC[-28]),RC[-27]-VLOOKUP(RC[-28],Sheet1!C[-28]:C[-27],2,1),0)

 

[Blunder1]

Thank you for the hints but they're not alot difference in the speed. Still i have learnt something new and i appreciate that

Blunder