+ Reply to Thread
Results 1 to 2 of 2

Convert INDEX, AGGREGATE to Macro

  1. #1
    Registered User
    Join Date
    05-25-2016
    Location
    Markham,ON
    MS-Off Ver
    2010
    Posts
    6

    Exclamation Convert INDEX, AGGREGATE to Macro

    Not sure if this is possible, but I am looking to convert my function for 1 cell into a function for 365 cells. The current function references too many cells, which is slowing dozn my excel significantly.

    Here is my current function:
    =INDEX('Données forage wenco'!$E:$E,AGGREGATE(14,6,--('Données forage wenco'!$C:$C&'Données forage wenco'!$D:$D&'Données forage wenco'!$G:$G='Données forage'!$D6&'Données forage'!Z$4&'Données forage'!Z$2)*ROW('Données forage wenco'!$L:$L),1))

    It searches the 'Donneés forage wenco' data sheet being referenced, and 'Donneés forage' is the sheet where the output should be displayed. I am wondering if this can be made into a macro so that run time and lag can be cut down? The only cells that change are D6, as it will grow higher. If someone could convert this to a macro (if possible?) that would be highly appreciated.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Convert INDEX, AGGREGATE to Macro

    'Données forage wenco'!$E:$E
    you refer to the whole column, which is >1 mln row in excel 2016.

    Use a range that is great enough to match all data e.g.

    'Données forage wenco'!$E1:$E5000
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. Macro to aggregate data from date to date
    By mat83400 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-05-2016, 06:35 AM
  2. Index, Aggregate, Row - Removing Duplicate W/ Index Help
    By adbexcel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-04-2016, 03:53 PM
  3. Macro to lookup data monthwise and aggregate the value in a new sheet
    By yemnirmal in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-13-2015, 05:36 AM
  4. Excel query/macro , delete records where the aggregate value is not over 100
    By guitarplyr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-17-2015, 02:59 PM
  5. INDIRECT, INDEX, AGGREGATE?? Help!
    By bbr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-26-2013, 08:03 PM
  6. Macro to aggregate if matching
    By paperwings25 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2012, 05:22 PM
  7. Macro for converting aggregate to case level data
    By epi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-27-2010, 09:45 AM

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