+ Reply to Thread
Results 1 to 8 of 8

Array to Aggregate (or VBA?)

  1. 11-04-2018, 01:15 PM #1
    BrianDove32
    BrianDove32 is offline
    Registered User
    Join Date
    11-04-2018
    Location
    Mauritius
    MS-Off Ver
    Office 2018
    Posts
    4

    Lightbulb Array to Aggregate (or VBA?)

    Hi,

    I have a wide range of array formula in a sheet but cannot afford to go through each one of them individually and pressing Ctrl + Shift + Enter everytime. After some research, I found there 2 ways that can alleviate this issue. Either by converting the formula to an AGGREGATE formula (preferable) or do it via VBA coding. Ive tried and watch many youtube videos in vain.

    =INDEX(Database!C4:C1200,SMALL(IF($L$3=Database!G4:G1200,ROW(Database!G4:G1200)-ROW(Database!G4)+1),2))

    Then for each cell down, it increases the n-th number of the formula at the end, so it goes on:

    =INDEX(Database!C4:C1200,SMALL(IF($L$3=Database!G4:G1200,ROW(Database!G4:G1200)-ROW(Database!G4)+1),3))
    =INDEX(Database!C4:C1200,SMALL(IF($L$3=Database!G4:G1200,ROW(Database!G4:G1200)-ROW(Database!G4)+1),4))
    =INDEX(Database!C4:C1200,SMALL(IF($L$3=Database!G4:G1200,ROW(Database!G4:G1200)-ROW(Database!G4)+1),5))
    .....
    =INDEX(Database!C4:C1200,SMALL(IF($L$3=Database!G4:G1200,ROW(Database!G4:G1200)-ROW(Database!G4)+1),40))


    Can anyone help please ? thanks a lot!!! much appreciated

    Brian
    Reply With QuoteRegister To Reply
  2. 11-04-2018, 01:19 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,779

    Re: Array to Aggregate (or VBA?)

    Welcome to the forum!

    Try this:

    =INDEX(Database!$C$4:$C$1200,SMALL(IF($L$3=Database!$G$4:$G$1200,ROW(Database!$G$4:$G$1200)-ROW(Database!G4)+1),ROWS(G$3:G4)))

    Notice the addition of the dollar signs to anchor the ranges.

    Confirm as an array formula, and once you have done that, drag copy down.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 11-04-2018, 01:30 PM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,779

    Re: Array to Aggregate (or VBA?)

    This forum requires you to tell us if you have posted the same question elsewhere. I will post a link for you this time: https://www.excelguru.ca/forums/show...gate-(or-VBA-)

    Next time you do this you MUST tell us yourself.
    Reply With QuoteRegister To Reply
  4. 11-05-2018, 02:46 AM #4
    BrianDove32
    BrianDove32 is offline
    Registered User
    Join Date
    11-04-2018
    Location
    Mauritius
    MS-Off Ver
    Office 2018
    Posts
    4

    Exclamation Re: Array to Aggregate (or VBA?)

    Hi AliGW!

    Thanks for your help and apologies for duplicate thread, thought it was 2 separate/different forums with different people...! However, its not working still.

    Put differently, I would like that when a value in column "Database!$I$4:$I$1200" contains (not exact match) what's in $L$3, a cell in sheet "A" displays the data from the same row but different column "Database!$C$4:$C$1200".

    Hope that makes sense ? I am also not sure where does the "ROW(Database!G4)+1),ROWS(G$3:G4)))" comes from in your formula as there is no data in those cells.

    Thanks a million!!!!


    Thanks
    Brian
    Last edited by BrianDove32; 11-05-2018 at 02:48 AM.
    Reply With QuoteRegister To Reply
  5. 11-05-2018, 02:52 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,779

    Re: Array to Aggregate (or VBA?)

    Yes, they are two forums - I just happen to be a moderator on both. You obviously haven't read the rules yet on either. Please do so now.
    Reply With QuoteRegister To Reply
  6. 11-05-2018, 03:32 AM #6
    BrianDove32
    BrianDove32 is offline
    Registered User
    Join Date
    11-04-2018
    Location
    Mauritius
    MS-Off Ver
    Office 2018
    Posts
    4

    Re: Array to Aggregate (or VBA?)

    Hi AligW,

    Apologies again. Could you please delete the post on the other forum ? Ive read it now


    Thanks in advance for checking my reply above in regards to the formula which is not working

    Thanks again for your help

    Brian
    Reply With QuoteRegister To Reply
  7. 11-05-2018, 04:28 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,779

    Re: Array to Aggregate (or VBA?)

    No, there is no need for either thread to be deleted. Cross-posting is not disallowed, you just have to disclose it. The rules on both forums make this clear - read them again.

    I will reply to your query when you provide a sample workbook that I can check - it would just be guesswork otherwise.
    Reply With QuoteRegister To Reply
  8. 11-05-2018, 04:30 AM #8
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Array to Aggregate (or VBA?)

    The non ARRAY format with aggregate Function is

    Please Login or Register  to view this content.
    Enter as normal formula.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Aggregate Spacing
    By Median in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2018, 01:23 PM
  2. Aggregate by ID in DAX
    By JB_100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2017, 02:35 AM
  3. Object required error AGGREGATE COLUMNS TO ARRAY FUNCTION
    By LeoDan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-30-2016, 02:50 PM
  4. [SOLVED] Aggregate forumla help
    By seanyc in forum Excel General
    Replies: 13
    Last Post: 06-15-2012, 02:42 PM
  5. How to aggregate data?
    By bumblebee88 in forum Excel General
    Replies: 3
    Last Post: 02-21-2012, 04:14 PM
  6. Functions-help for aggregate tab
    By kmsoni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-22-2011, 03:10 AM
  7. Best way to aggregate?
    By gnome_core in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2009, 04:38 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1