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

Originally Posted by BenHatton

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!

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.

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

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?

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.

Very good! Thank you VERY much.