+ Reply to Thread
Results 1 to 17 of 17

Other option then Array

  1. #1
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Other option then Array

    Hey Guys

    i have the attached Excel that uses many many Array formulas, which is making the document incredibly slow.

    do you guys see any other way to make the attached ? maybe with VBA or something?

    regards
    Attached Files Attached Files

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,553

    Re: Other option then Array

    Hi,

    The only reason it's so slow is that you're referencing entire columns within those array formulas, e.g.

    =IFERROR(TEXTJOIN("; ",,IF(P16=Invoice!$B:$B,Invoice!$A:$A,"")),"")

    As such, just one instance of those TEXTJOIN formulas is being forced to calculate over more than 2 million cells, an astonishing number for a single formula. What's more, the Invoice sheet only contains 3 entries!

    Choose a suitably low, though sufficient, end row reference instead, e.g. A2:A100. Or else used dynamically-defined Named Ranges.

    Regards
    Last edited by XOR LX; 01-29-2019 at 01:37 AM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Quote Originally Posted by XOR LX View Post
    Hi,

    The only reason it's so slow is that you're referencing entire columns within those array formulas, e.g.

    =IFERROR(TEXTJOIN("; ",,IF(P16=Invoice!$B:$B,Invoice!$A:$A,"")),"")

    As such, just one instance of those TEXTJOIN formulas is being forced to calculate over more than 2 million cells, an astonishing number for a single formula. What's more, the Invoice sheet only contains 3 entries!

    Choose a suitably low, though sufficient, end row reference instead, e.g. A2:A100. Or else used dynamically-defined Named Ranges.

    Regards
    the issue, is this document is a template budget for a company which can have over 10.000 invoices, the file that i loaded is just an example. the same applies to the requisitions

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,553

    Re: Other option then Array

    1 million is still 100 times more than 10,000.

    If 15,000 is a sufficient upper limit, use that. Better still, use dynamically-defined ranges which automatically detect the last-used rows in the relevant columns and reference those in the formulas instead.

    Regards

  5. #5
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Quote Originally Posted by XOR LX View Post
    1 million is still 100 times more than 10,000.

    If 15,000 is a sufficient upper limit, use that. Better still, use dynamically-defined ranges which automatically detect the last-used rows in the relevant columns and reference those in the formulas instead.

    Regards
    Thank you for the advice, not very familiar with Dynamic defines ranges, is that the Offset formula in name range under formula?

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,553

    Re: Other option then Array

    I prefer to use non-volatile INDEX set-ups, myself. It is unfortunate that so many Excel sites promote the use of the volatile OFFSET in such cases.

    I'm more concerned here about your intentions in this workbook. I take it you realise that TEXTJOIN is designed to concatenate text into a single cell? So potentially you'd want to have up to 10,000 entries put into a single cell? This doesn't seem like a very good idea. What's more, you should know that that function has a limit of 32,767 characters: any more than that and it will error.

    Regards

  7. #7
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Quote Originally Posted by XOR LX View Post
    I prefer to use non-volatile INDEX set-ups, myself. It is unfortunate that so many Excel sites promote the use of the volatile OFFSET in such cases.

    I'm more concerned here about your intentions in this workbook. I take it you realise that TEXTJOIN is designed to concatenate text into a single cell? So potentially you'd want to have up to 10,000 entries put into a single cell? This doesn't seem like a very good idea. What's more, you should know that that function has a limit of 32,767 characters: any more than that and it will error.

    Regards
    Dear XOR LX,

    there will not be all of the 10,000 invoiced in one cell, and we will not exceed the 37,000 characters limitation.

    i never used the INDEX function to name range, any possibility to show me how?
    regards

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,553

    Re: Other option then Array

    A typical set-up for the Invoice sheet would be to define, in Name Manager:

    Name: Invoice_LRow
    Refers to: =MATCH(REPT("z",255),Invoice!$C:$C)

    which automatically detects the last-used row in that worksheet, in this case using column C (Company) as a basis.

    Note that the choice of formula in the Refers to field is dependent on the datatype of the column chosen to determine the last-used row. I used REPT("z",255) here since the column C entries are text; if they had been numerics, I would have chosen 88^88 instead. Slightly different constructions are required if the column chosen contains either a) a mixture of text and numerics or b) null strings (""), e.g. as a result of formulas within those cells. However, usually we can find a column to use which is either purely text or purely numerical and which does not contain any null strings.

    You can then define:

    Name: Invoice_Invoice_ID
    Refers to: =Invoice!$A$2:INDEX(Invoice!$A:$A,Invoice_LRow)

    Name: Invoice_Requisition_ID
    Refers to: =Invoice!$B$2:INDEX(Invoice!$B:$B,Invoice_LRow)

    and reference these in the formulas instead.

    Regards

  9. #9
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Dear XOR LX

    tried to replace the value in the Array formula with the name range but i am not getting any results.
    {=IFERROR(TEXTJOIN("; ",,IF(E16=RequisitionB,Requisition,"")),"")}

    the original array formula was as below
    {=IFERROR(TEXTJOIN("; ",,IF(E17=Requisition!$B:$B,Requisition!A:A,"")),"")}

    any suggestions?

  10. #10
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,553

    Re: Other option then Array

    Can I see your updated workbook?

    Regards

  11. #11
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Quote Originally Posted by XOR LX View Post
    Can I see your updated workbook?

    Regards
    here is is attached
    Attached Files Attached Files

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,081

    Re: Other option then Array

    Please change

    RequisitionB to
    =Requisition!$A$2:INDEX(Requisition!$A:$A,COUNTA(Requisition!$B:$B))

    Requisition and RequisitionB need to have the same no. of rows.

  13. #13
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Quote Originally Posted by Bo_Ry View Post
    Please change

    RequisitionB to
    =Requisition!$A$2:INDEX(Requisition!$A:$A,COUNTA(Requisition!$B:$B))

    Requisition and RequisitionB need to have the same no. of rows.
    should i change this in the name range?

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,081

    Re: Other option then Array

    Yes, you should

  15. #15
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Quote Originally Posted by Bo_Ry View Post
    Yes, you should
    i tried the change and it still doesn't work

  16. #16
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,081

    Re: Other option then Array

    Please see attached

    2019-01-31 12_20_22-Budget Template for Marwan.xlsx - Excel.png

    Sorry should be Requisition not RequisitionB

    =Requisition!$A$2:INDEX(Requisition!$A:$A,COUNTA(Requisition!$B:$B))
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    122

    Re: Other option then Array

    Quote Originally Posted by Bo_Ry View Post
    Please see attached

    Attachment 608995

    Sorry should be Requisition not RequisitionB

    =Requisition!$A$2:INDEX(Requisition!$A:$A,COUNTA(Requisition!$B:$B))
    OK that worked. thank you

    do i do the same for the Invoice now?

+ 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. [SOLVED] Optimization - non-array option?
    By Kanuck in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-26-2017, 01:12 PM
  2. Replies: 9
    Last Post: 03-26-2017, 04:04 AM
  3. [SOLVED] Which option is faster? Passing a 2D Array
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-06-2016, 06:54 PM
  4. Replies: 2
    Last Post: 08-15-2013, 12:25 PM
  5. [SOLVED] Multiple option buttons - Need VBA to disable second group of option buttns based on respo
    By ex123 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-19-2013, 03:30 AM
  6. Array Formula 4 Criteria, One of them Multiple Option
    By excelmabel2013 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-12-2013, 02:26 PM
  7. Array Formula: can anyone suggest a better option?
    By Neil07979 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-15-2007, 08:25 AM

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