VBA: Using PercentRank on dynamically changeable subsets

[ender64]

Hello and thank you for your time and help,

I am trying to create a function which would extract a subset from a given range depending on the values contained in another range. The final aim is to be able to use the Percentrank function on dynamically changeable groups

A	B	C	D	E
1	Country	GDP	Group	PercentRank
2	Albania	5000	2	?
3	France	28000	1
4	Romania	6000	2
5	US	34000	1

The idea would to have something like Percentrank(Subset(B2:B4, C2:C4, 1), B2) with the Subset( TotalRange, GroupRange, GroupNumber) function returning the subset as a range.

From there, all I would need to do is to change a country's group to update all the ranks.

I have started with something like this but it doesn't seem to work...

Please Login or Register  to view this content.

Any thoughts?

Thank you in advance

[Richard Buttrey]

Hi,

Why not just use Data Advanced Filter to extract the subset to another sheet/area based on the criteria in your 'other range' and use this extracted data with your ranking formula?

[ender64]

Hi Richard,

Thank you for your answer. I'd actually thought about something similar but the datasheet I am using is far more complex than my example, and implementing these filters would require a lot of successive worksheets and filters.

I was just surprised that there was no existing function to extract a subset from a range. Do you have any idea why my code doesn't work?
It doesn't even return a Range which I find surprising. Does the union function work with Range and Cell arguments?

If I can't figure it out, I will definitely use your idea but I feel like I'm pretty close

Thanks again,

Benjamin

[Richard Buttrey]

Hi,

The Union instruction works with ranges and cells and since a cell is a range then the answer to your question is Yes.

I'm still not convinced though that you need to build a UDF at all. It's probable that a standard array formula could gather your subset to which you could apply your PercentRank function, or if not then as I said before a Data Advanced Filter (which is just a single line of macro code) could extract a separate subset of your data which can then be analysed with the PercentRank function.

I'm not clear why filtering would require successive sheets. I generally find that having one filter output area and changing either or both the criteria cells and the Output column labels meets all requirements. And the data filtering technique has the huge advantage that it's far far quicker than any VBA code, particularly looping code.

If you'd like to upload a representative example of your data, with a separate results area that you've manually created, along with any notes about how you arrived at it then perhaps we can help further.