hi,
Below is the array formula which is taking too much time on calculation process. Is there any other formula which can perform faster.
Please Login or Register to view this content.
hi,
Below is the array formula which is taking too much time on calculation process. Is there any other formula which can perform faster.
Please Login or Register to view this content.
Don't use full-column references like $D:$D in array formulae, as every cell in that range will be examined (as well as E:E, F:F, and G:G). Make your ranges just as large as required.
You could also think about using SUMIFS if the value you are looking for in column G is numeric.
Hope this helps.
Thanks, I will try with required ranges.
As Pete says, don't use full column references in Array Formulae, ever.
Try it this way:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
This puts the Formula to Value conversion back in:
Please Login or Register to view this content.
Hi Trevor,
it is not made clear, but it might be that the formulae are placed in a different sheet than Daily_Summary (as they use B2= ... , N2= ...etc.), so LastRow might be different on the two sheets.
Pete
Good thought Pete.
Maybe this then:
Please Login or Register to view this content.
Why limit yourself to column A? Since EVERY nonblank cell is in UsedRange, LastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1 would be the bottommost row in use in any column in UsedRange.
That said, if this is going to be an array formula anyway, make it even more efficient by skipping the multiplication.
"=IFERROR(INDEX(Daily_Summary!"$G$2:$G$" & LastRow & ",MATCH(TRUE," & _
"IF(B2=Daily_Summary!$D$2:$D$" & LastRow & "," & _
"IF(N2=Daily_Summary!$E$2:$E$" & LastRow & "," & _
"P2=Daily_Summary!$F$2:$F$" & LastRow & ")),0)),0)"
If you're ultimately converting this to values anyway, if the worksheet containing these results and Daily_Summary both have distinct column labels in row 1, you could use MS Query to create an outer join of the 2 ranges as tables. This sort of operation is a table join, so a database approach to performing it may be the most efficient way.
@Pete & TMS
Thanks, both your suggestion and code works perfectly.
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks