hi all,
i does anyone have an idea how to make a faster calculation for a
comperacy between 2 columns in order to bring a third colulm?
index/match is very problematic,i know that vlookup will probbly be
more safiecent,does anyone have an idea?
(E-Mail Removed) כתב:
> hi scott,
> i'm working with a data above 12900 rows(alot of calculation time-the
> time ok for vlookup).
> i tried to think about a vlookup idea but didnt find a solution for 2
> columns comparison.
> this is my formula for now:
> "=INDEX(filename & "'!R2C6:R10000C6,MATCH(1,(VALUE(RC[-10])=filename &
> "'!R2C1:R10000C1)*(RC[1]>=LEFT(filename &
> "'!R2C4:R10000C4,len(RC[1])))*(RC[1]<=RIGHT( filename &
> "'!R2C4:R10000C4,len(RC[1]))),0))"
>
> Scott wrote:
> > Anychance you can post that code? I'll see if I can find anything to help.
> >
> > Just out of curiosity, how big is the data that you're using? you could
> > always use a Vlookup (or Hlookup table) and code that in. If it's taking
> > long for an array like this, maybe just try it with a Vlookup. Even ifthe
> > main code takes a bit longer you should be able to save on recalculation
> > time. See if this might help, if not post your code and I'll see what I can
> > do.
> >
> > Cheers,
> > Scott
> >
> > "(E-Mail Removed)" wrote:
> >
> > > hi scott,
> > > your suggestion seems to be resounble for me i just dont know how to
> > > implemet it.
> > > my problematic array formula is based on index and math
> > > for ex.
> > > C B A
> > > 123 a-d !
> > > 123 e-g #
> > > 456 a-d &
> > > 456 e-g ~
> > >
> > > so for example i need to bring the data from third culomn
> > > so i have 123 from column A, c from column B
> > > by the index match it will bring the "!" as a value from column C
> > >
> > > for now i found a solution with index match by true value from the 3
> > > statment it will bring the third column.these are the statment:
> > > equal number from column A
> > > c is is equal or bigger then left value of the range
> > > c is equal or smaller then right value of the range
> > >
> > > but this formula is making heavy re-calculation
> > > do you have maybe a formula that will be much lighter then my current
> > > for the calculation stage?
> > >
> > > Scott כתב:
> > > > You'll only have false values if you later update a section that was a
> > > > dependant variable for another. For example, if you have column A as some
> > > > random data and in column B you multiplied it by 4, then when you
> > > > pastespecial you'll only get whatever values were there before you copied it
> > > > (not to mention you lose the formulai as well). So in response to your
> > > > question, yeah. By doing this before doing a recalculation you will not have
> > > > the same values, because there will be nothing to calculate. It will only be
> > > > values, no formulai.
> > > >
> > > > What I suggest is that you run you're program in manual all the way.. At the
> > > > end don't turn it back to automatic. If you wrote it in such a waythat you,
> > > > or the macro, did all the calculation then you won't need the recalculate and
> > > > you can get rid of the recalculation. However, if you find that a lot of
> > > > your cells are wrong under manual calculation and the auto calculation
> > > > corrects this then you will either have to stick with the longer recal times
> > > > or fix up your code a bit so you don't need to recalculate.
> > > >
> > > > (just in case you don't know what I mean, if you have say ten numbers in
> > > > column A and in column B you have B1=A1+5 etc, if you copy that and paste it
> > > > down till the end of column A then you will need to recalculate. if you set
> > > > up a loop like
> > > > dim i as integer
> > > > for i = 1 to 10
> > > > Cells(i, 2) = Cells(i, 1) + 5
> > > > next i
> > > > then the macro does all the calculation and you don't need to recalculate)
> > > >
> > > > cheers,
> > > >
> > > > Scott
> > > >
> > > > "(E-Mail Removed)" wrote:
> > > >
> > > > > by making the copy pastspecial before doing re-calcualtion,i might have
> > > > > a false values,am i correct?
> > > > >
> > > > > Scott wrote:
> > > > > > by going back to autocalculation excel recalculates all cells that it has
> > > > > > flagged for recalculation. so one of two ways to avoid this. Don't turn
> > > > > > calculation back to automatic... not really recommended or justcopy
> > > > > > everything you created and paste it over itself as values only (ie
> > > > > > cells.pastespecial xlpastevalues) then turn the calculation back to auto.
> > > > > > This way there are no formulas which may need updating
> > > > > >
> > > > > > cheers,
> > > > > > Scott
> > > > > >
> > > > > > "(E-Mail Removed)" wrote:
> > > > > >
> > > > > > > hi all,
> > > > > > > i build some macro in excel based on access,
> > > > > > > at the start i'm doing manual calculate in the code so it will not
> > > > > > > desturn the proccessing
> > > > > > > but in the end of my automation i'm bringing back the
> > > > > > > automatecalculation but then the proccessing is taking alot of time
> > > > > > > even more then the accually vba proccessing,
> > > > > > > how can i faster this issue?
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
Similar Threads
