Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare tabs using Double Lookup, in VB code
[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,P reviousUnitList,0),MAT CH(D$1,PreviousDateHeader,0))),0,(OFFSET(PreviousB ase,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,P reviousUnitList,0),MAT CH(D$1,PreviousDateHeader,0))),0,(OFFSET(PreviousB ase,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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare tabs using Double Lookup, in VB code
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 wrote: [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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare tabs using Double Lookup, in VB code
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare tabs using Double Lookup, in VB code
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 Ricky Pang wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare tabs using Double Lookup, in VB code
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
compare two data on two tabs | Excel Discussion (Misc queries) | |||
Printing two tabs of the same worksheet to one double-sided page | Excel Discussion (Misc queries) | |||
Compare two tabs and only show exceptions | Excel Discussion (Misc queries) | |||
Compare tabs using Double Lookup, in VB code | Excel Programming |