+ Reply to Thread
Results 1 to 5 of 5

Changing Array Formula

  1. #1
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Changing Array Formula

    Hi All, looking to change this formula from an array to NOT array formula:
    The purpose of the formula is to retrieve all values in $H on '2018CSV' in a list according to the $A$9 value. But since it's an array formula I cannot reorder the rows.

    =IFERROR(INDEX('2018CSV'!$H$1:$H$100000,SMALL(INDEX(($A$9='2018CSV'!$A$1:$A$100000)*(MATCH(ROW('2018CSV'!$A$1:$A$100000), ROW('2018CSV'!$A$1:$A$100000)))+($A$9<>'2018CSV'!$A$1:$A$100000)*1048577,),ROWS('2018CSV'!$A$1:A1))), "-")

    Any help would be awesome.

    Thanks!

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Changing Array Formula

    =IFERROR(INDEX('2018CSV'!$H$1:$H$100000,aggregate(15,6,ROW('2018CSV'!$A$1:$A$100000)/($A$9='2018CSV'!$A$1:$A$100000),ROWS($A$1:A1))),"-")

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Changing Array Formula

    Depending on how AGGREGATE is used, it is still volatile, like an array formula, and I believe this is one of those times.

    If I am working with large volumes of data, and an array formula would slow me down (or to just simplify the process) I often use a helper column to pull out the data that the array would.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Re: Changing Array Formula

    Thanks Dibbins, the helper column may be a good option in this case. Though I wish I could figure it out to lose the array.

    Thx,

  5. #5
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Re: Changing Array Formula

    Thanks as well Tim. I think that formula is working, I will test it out- all well I will mark the thread solved.

    Thx again guys,

+ 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. Changing formula to NOT array
    By JPSIMMON in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-20-2017, 02:47 PM
  2. [SOLVED] Array formula not changing rows for an unlocked range when run in VBA
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-02-2014, 10:34 AM
  3. Array Formula should adjust to changing range
    By Shinga in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2013, 05:38 AM
  4. [SOLVED] Formula for a continuously changing array
    By gururajendrak in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 03-01-2013, 12:05 PM
  5. Formula to answer Y/N to a value changing (More than once) in an array?
    By agf12555 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-17-2012, 11:17 PM
  6. Filling Down with an Array Formula - Changing Relative Reference
    By fervorking in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2011, 06:29 PM
  7. Replies: 4
    Last Post: 05-04-2005, 12:06 PM

Tags for this Thread

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