+ Reply to Thread
Results 1 to 14 of 14

Help to simplify formula with VBA

  1. #1
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    125

    Help to simplify formula with VBA

    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: 
    Attached Files Attached Files
    Last edited by zhead; 03-04-2020 at 03:31 PM. Reason: add sample file

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Help to simplify formula with VBA

    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

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,873

    Re: Help to simplify formula with VBA

    @hemesh
    All those functions should be available in 2010 version of Xl

  4. #4
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    125

    Re: Help to simplify formula with VBA

    I updated my profile to include Excel 365

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,873

    Re: Help to simplify formula with VBA

    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))

  6. #6
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    125

    Re: Help to simplify formula with VBA

    Fluff13 it looks like this may be the perfect fit. let me do some testing with the full data. will let you know.
    Thanks

  7. #7
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Help to simplify formula with VBA

    @ fluff in Excel 2016 Version I got
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I Asked OP due to blue part

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,873

    Re: Help to simplify formula with VBA

    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

  9. #9
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Help to simplify formula with VBA

    Thanks for the info fluff, I never noticed this thing that we can make it to work like this !

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,873

    Re: Help to simplify formula with VBA

    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.

  11. #11
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    125

    Re: Help to simplify formula with VBA

    @ Fluff13 the one using Transpose is wonderful in excel 365. Thanks again
    The other 2 would they have to be copied Down and Across?

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,873

    Re: Help to simplify formula with VBA

    You're welcome & thanks for the feedback

    If you are talking about the formulae in posts7 & 8, they are your existing formula.

  13. #13
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    125

    Re: Help to simplify formula with VBA

    Yes those are the ones. Thanks.
    I will mark this resolved

  14. #14
    Forum Contributor
    Join Date
    09-05-2013
    Location
    Spring, Tx
    MS-Off Ver
    Excel 365
    Posts
    125

    Re: Help to simplify formula with VBA

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Simplify Formula
    By alonsogtz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2015, 08:05 PM
  2. [SOLVED] Simplify the formula
    By eing in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2013, 12:44 AM
  3. Simplify formula
    By SalamanderSam14 in forum Excel General
    Replies: 6
    Last Post: 01-04-2012, 12:23 PM
  4. Excel 2007 : Help me simplify a formula
    By dip11 in forum Excel General
    Replies: 3
    Last Post: 11-08-2011, 11:17 AM
  5. Simplify Formula
    By sparx in forum Excel General
    Replies: 14
    Last Post: 01-27-2009, 02:40 PM
  6. Simplify a Formula
    By alan_stephen75@ in forum Excel General
    Replies: 8
    Last Post: 02-20-2008, 09:32 AM
  7. [SOLVED] How to simplify this IF formula
    By Bojana in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2005, 12:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1