LinkBack URL
About LinkBacks
I have a spread sheet with over 300,000 rows and 40 columns. I use the formula below in each cell. This takes forever to load and refresh. is there any way to make this faster?
HTML Code:
I have a spread sheet with over 300,000 rows and 40 columns. I use the formula below in each cell. This takes forever to load and refresh. is there any way to make this faster?
HTML Code:
Last edited by zhead; 03-04-2020 at 03:31 PM. Reason: add sample file
Are you sure you are using Excel 2010 as the functions used in your worksheet does not belong to Excel version 2010. Please update your version in your profile so that people can tailor the answer based on your requirement and specific to the version.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
@hemesh
All those functions should be available in 2010 version of Xl
I updated my profile to include Excel 365
In that case if you have the dynamic arrays try this in C2 copied down, but NOT across
=TRANSPOSE(FILTER('SQL DATA'!$Y$2:$Y$172679,'SQL DATA'!$A$2:$A$172679=$A2))
Fluff13 it looks like this may be the perfect fit. let me do some testing with the full data. will let you know.
Thanks
@ fluff in Excel 2016 Version I got
Formula:
=IFERROR(_xlfn.SINGLE(INDEX('SQL DATA'!$Y$2:$Y$172679,AGGREGATE(15,6,ROW('SQL DATA'!$Y$2:$Y$172679)/('SQL DATA'!$A$2:$A$172679=$A2),COLUMNS($X2:Y2))-ROW($X$2)+1)),"")&""
I Asked OP due to blue part
I'm so used to seeing the @ symbol in formulae now I never noticed it, I was just looking at the functions.
If you remove the @ sign & use
=IFERROR(INDEX('SQL DATA'!$Y$2:$Y$500000,AGGREGATE(15,6,ROW('SQL DATA'!$Y$2:$Y$500000)/('SQL DATA'!$A$2:$A$500000=$A2),COLUMNS(Y2:$Y2))-ROW($Y$2)+1),"")&""
it should work in 2010 & later
Thanks for the info fluff, I never noticed this thing that we can make it to work like this !
You're welcome & thanks for the rep
@zhead I'd definitely like to know how it compares speed wise, as I've never used these new functions on large datasets.
@ Fluff13 the one using Transpose is wonderful in excel 365. Thanks again
The other 2 would they have to be copied Down and Across?
You're welcome & thanks for the feedback
If you are talking about the formulae in posts7 & 8, they are your existing formula.
Yes those are the ones. Thanks.
I will mark this resolved
Fluff13 I would like to thank you one more time. I was able to use the formula and my vlookup in a couple macros to automate the process.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks