+ Reply to Thread
Results 1 to 9 of 9

Ignore specific colums from Aggregate function

  1. #1
    Registered User
    Join Date
    07-15-2016
    Location
    Dublin, Ireland
    MS-Off Ver
    2013
    Posts
    22

    Ignore specific colums from Aggregate function

    Hi All,

    I have a table with Countries (A,B,C etc) and Cities (1,2,3 etc). A value is calculated for each Country and City.
    I then have a calculation to identify the top X number of values associated to the country/city.

    However, I do not want the country to be returned in these results. The rows that I want to include are not in bold (the rows that I do not want to include are in bold) and they are also on different rows.

    I would prefer not to have more than 1 table, and I would prefer not to have a column dedicated to the value for A,B,C etc and a separate column for the value for 1,2,3 etc. Unfortunately I have no understanding of VBA.

    I am struggling to explain this well, so I have attached a sheet of sample data where it is fairly self explanatory, and I hope you can understand from that!

    I think there are a few ways to solve this, but I can't seem to beat it! Perhaps by adding a "if bold" to =IF(I8=" "," ",INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($G$2:$G$9)-ROW($G$2)+1)/($G$2:$G$890=K8),COUNTIF(K$7:$S8,K8))))
    Any help or suggestions would be much appreciated.

    Thanks,
    Ben
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-21-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office 2016
    Posts
    146

    Re: Ignore specific colums from Aggregate function

    Quote Originally Posted by BenHatton View Post
    Hi All,

    I have a table with Countries (A,B,C etc) and Cities (1,2,3 etc). A value is calculated for each Country and City.
    I then have a calculation to identify the top X number of values associated to the country/city.

    However, I do not want the country to be returned in these results. The rows that I want to include are not in bold (the rows that I do not want to include are in bold) and they are also on different rows.

    I would prefer not to have more than 1 table, and I would prefer not to have a column dedicated to the value for A,B,C etc and a separate column for the value for 1,2,3 etc. Unfortunately I have no understanding of VBA.

    I am struggling to explain this well, so I have attached a sheet of sample data where it is fairly self explanatory, and I hope you can understand from that!

    I think there are a few ways to solve this, but I can't seem to beat it! Perhaps by adding a "if bold" to =IF(I8=" "," ",INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($G$2:$G$9)-ROW($G$2)+1)/($G$2:$G$890=K8),COUNTIF(K$7:$S8,K8))))
    Any help or suggestions would be much appreciated.

    Thanks,
    Ben
    Get file here!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-15-2016
    Location
    Dublin, Ireland
    MS-Off Ver
    2013
    Posts
    22

    Re: Ignore specific colums from Aggregate function

    Thank you! That ignores all of the letters, perfectly! Unfortunately this has removed the dynamic table, whereby any number inserted into J2, the yellow highlighted cell- returns that number of results in the dynamic table.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Ignore specific colums from Aggregate function

    The number of results displayed in the table could be handled with a couple of conditional formatting rules. The first rule, applied to I8:I15, would change the font to match the light blue background: =I8>J$2
    The second rule, applied to J8:K15, would change the color of the font to match the white background: =$I8>$J$2
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    07-15-2016
    Location
    Dublin, Ireland
    MS-Off Ver
    2013
    Posts
    22

    Re: Ignore specific colums from Aggregate function

    Thinking outside the box! I like it, thank you!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Ignore specific colums from Aggregate function

    You're Welcome and thank you for the feedback. If that takes care of your original question, please select Thread Tools from the menu link above your first post and mark this thread as SOLVED. I hope that you have a blessed day.

  7. #7
    Registered User
    Join Date
    07-15-2016
    Location
    Dublin, Ireland
    MS-Off Ver
    2013
    Posts
    22

    Re: Ignore specific colums from Aggregate function

    Ah, unfortunately when implementing the formula I realised that it is using the 'ifnumber' function. The actual data would not use a split between letters and numbers between country/city.

    Is there a way to use the same method, but using something like 'ifbold' instead?

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Ignore specific colums from Aggregate function

    No ISBOLD formula (unless you make one with vba), but there is an ISFORMULA formula, and only the bold rows have a formula in column B, so that can be used to ignore them.

    Try these array formulas

    In K8

    =IFERROR(LARGE(IF(ISFORMULA($B$2:$B$9),FALSE,$G$2:$G$9),I8),"")

    In J8

    =IF(I8=" "," ",INDEX($A$2:$A$9,MIN(IF(NOT(ISFORMULA($B$2:$B$9))*($G$2:$G$9=K8)*ISERROR(MATCH($A$2:$A$9,I$7:I7,0)),ROW($A$2:$A$9)-ROW($A$2)+1))))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  9. #9
    Registered User
    Join Date
    07-15-2016
    Location
    Dublin, Ireland
    MS-Off Ver
    2013
    Posts
    22

    Re: Ignore specific colums from Aggregate function

    Very good! Thank you VERY much.

+ 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] Aggregate function is not woring
    By shukla.ankur281190 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-19-2016, 07:15 AM
  2. Function AGGREGATE and FREQUENCY
    By XLalbania in forum Excel General
    Replies: 9
    Last Post: 02-22-2016, 05:11 PM
  3. [SOLVED] AGGREGATE Function
    By chief_abound in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2015, 12:48 AM
  4. [SOLVED] AGGREGATE function, If 0 show no value
    By antexity in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-07-2015, 11:59 AM
  5. Aggregate Function Using Sum
    By gtbaseball7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 02:19 PM
  6. Aggregate Function
    By nav505 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2013, 03:20 AM
  7. Aggregate function
    By stefantem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 04:47 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