Compare tabs using Double Lookup, in VB code

  • Thread starter Thread starter Ricky Pang
  • Start date Start date
R

Ricky Pang

[I had to repost as my original message from 3 days ago crashed]
Hello Experts,
This is an effort to speed things up. I am using 248 out of a possible
256 columns, and 150 rows down...and it's slow. Each cell in my
"Master" tab has this long formula, starting from D4 down and across.
It compares the "Current" tab with the "Previous" tab and produces a
result. Is there a way to convert this formula into code? and have it
in every cell, from D4 to IN150, so that it'll run faster?

This formula is from "Master" tab, cell [D4]:
=IF(ISBLANK(A4),"",

IF(OFFSET(CurrentBase,MATCH($A4,CurrentUnitList,0),MATCH(D$1,CurrentDate
Header,0))>IF(ISNA(OFFSET(PreviousBase,MATCH($A4,PreviousUnitList,0),MAT
CH(D$1,PreviousDateHeader,0))),0,(OFFSET(PreviousBase,MATCH($A4,Previous
UnitList,0),MATCH(D$1,PreviousDateHeader,0)))),"Y",

IF(OFFSET(CurrentBase,MATCH($A4,CurrentUnitList,0),MATCH(D$1,CurrentDate
Header,0))<IF(ISNA(OFFSET(PreviousBase,MATCH($A4,PreviousUnitList,0),MAT
CH(D$1,PreviousDateHeader,0))),0,(OFFSET(PreviousBase,MATCH($A4,Previous
UnitList,0),MATCH(D$1,PreviousDateHeader,0)))),"X","")))

Formula Interpretation:
CurrentBase is cell A1 of the "Current" tab
CurrentUnitList is cell A2:A150 of the "Current" tab
CurrentDateHeader is cell B1:IN1 of the "Current" tab
PreviousBase is cell A1 of the "Previous" tab
PreviousUnitList is cell A2:A150 of the "Previous" tab
PreviousDateHeader is cell B1:IN1 of the "Previous" tab

In the "Master" tab,
If [A4] is blank, then leave the cell empty.
If not;
Perform a double lookup by row [A4] and by column [D1] as the search
criteria.
Go to "Current" tab and get the intersect.
Go to "Previous" tab and get the intersect.
If the "Previous" intersect is [NAerror], then "0". Then, carry on with
the next comparison calculation.
If the "Current" intersect is > "Previous" intersect then the result is
"Y".
If the "Current" intersect is < "Previous" intersect then the result is
"X".
If it's neither > or <, then "" [blank cell].

I'm in need of a code in place of this formula.
Thank-you in advance.

Ricky
 
Ricky,

It took a bit of puzzling :)

Following would work for NUMERIC lookups. and IS LOTS AND LOTS faster
so you wouldn't need make it into values.

I Think you owe me one!


Sub MasterFill()

With Worksheets("Master")
.Names.Add Name:="cUnit", _
RefersToR1C1:="=MATCH(RC1,CurrentUnitList,0)"
.Names.Add Name:="cDate", _
RefersToR1C1:="=MATCH(R1c,CurrentDateHeader,0)"
.Names.Add Name:="pUnit", _
RefersToR1C1:="=MATCH(rC1,PreviousUnitList,0)"
.Names.Add Name:="pDate", _
RefersToR1C1:="=MATCH(R1c,PreviousDateHeader,0)"
.Names.Add Name:="cMatch", _
RefersToR1C1:="OFFSET(CurrentBase,cUnit,cDate)"
.Names.Add Name:="pMatch", _
RefersToR1C1:="=OFFSET(PreviousBase,pUnit,pDate)"
.Names.Add Name:="pMatchNoErr", _
RefersToR1C1:="=IF(ISNA(pMatch),0,pMatch)"

With .[d4:in150]
Application.Calculation = xlCalculationManual
.ClearContents
.Cells(1).Formula = _
"=IF(ISBLANK($A4),"""", LOOKUP(cMatch-pMatchNoErr," & _
"{-1E+32,0,1E-32},{""x"","""",""y""}))"
Application.StatusBar = "filling master..."
.FillRight
.FillDown
.Calculate
Application.StatusBar = "writing values..."
'.Formula = .Value
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False
End With
End With


End Sub






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Ricky Pang said:
[I had to repost as my original message from 3 days ago crashed]
Hello Experts,
This is an effort to speed things up. I am using 248 out of a possible
256 columns, and 150 rows down...and it's slow. Each cell in my
"Master" tab has this long formula, starting from D4 down and across.
It compares the "Current" tab with the "Previous" tab and produces a
result. Is there a way to convert this formula into code? and have
it in every cell, from D4 to IN150, so that it'll run faster?
Click to expand...
 
Hello,
Yes definitely, I owe you one for coming to my aid. Your macro answer
would really help expedite the number crunching process a lot faster. I
have one question as I couldn't get it to run properly. An excerpt of
your code:

"=IF(ISBLANK($A4),"""", LOOKUP(cMatch-pMatchNoErr," & _
"{-1E+32,0,1E-32},{""x"","""",""y""}))"

What does {-1E+32,0,1E-32} mean as it's quite different from my function
formula?

Thanks,
Ricky
 
is a lookup function with 2 arrays of constants :)

lookup(value,lookuparray,resultarray)

VALUE:
CurrentValue - PreviousValue (these are named formulas)

LOOKUPARRAY
very large negative value -1E^32
0 0
very small positive value 1E^-32 (almost but not quite 0)

RESULTARRAY
"x"
""
"y"


maybe a bit over the top.. but it's goal is to do the
big computes only once...


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Hello KeepITCool,
I thought to give you a status update on this project. Basically, I've
been getting #Value! errors on the entire range. My only workaround is
to use my lengthy formula throughout the range then paste as values once
it finishes its calculations. This avoids the continuous number
crunching, also avoids a bigger file size. I'd still like to see your
well thought-out code do it's magic though.

Thanks again.
Ricky
 
Back
Top