Monster vLookUp with Match
So, I have 2 files that are updated weekly from another process I built. They are about 2,200 rows by 66 columns.I need to make sure I am comparing like weeks and getting the percentage of change.
Monster formula
=IF(ISERROR(VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE)-
VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,OrevPlan!$A$1:$BM$1,0),FALSE)/
VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE)),0,
(VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE)-
VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))/
VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))
The Formula: =IF(ISERROR(VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE)
-VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,OrevPlan!$A$1:$BM$1,0),FALSE)
/VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))
,0,(VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE)
-VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))
/VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))
And how I build it in VBA
For X=2 to LastRow
For V=11 to StCol.MaxCols
Cells(X, V) = "=IF(ISERROR(VLOOKUP($A" & X & "," & CurSht _
& "!$A$2:$BK$" & lCur & ",MATCH(" & Cells(1, V).Address & "," & CurSht & "!$A$1:$BM$1,0),FALSE)-" _
& "VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev
& ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE)" _
& "/VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev _
& ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE)),0,(VLOOKUP($A" & X _
& "," & CurSht & "!$A$2:$BK$" & lCur & ",MATCH(" & Cells(1, V).Address & "," & CurSht _
& "!$A$1:$BM$1,0),FALSE)-VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev _
& ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE))" _
& "/VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev _
& ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE))"
Next V
Next X
After I had been working on this thing for 6 weeks (lots of other stuff going on); they said, “by the way, will it work if I compare 2 weeks that are a month apart?”
|