Top 10 extended for dups

I have a dataset of 1000 records that I am isolating the Top 5% and next 5% based on the locations current rank between (1-1000) and the locations current percentage. Since the top 5% would be the top 50 locations, I have that through use of the MultiRow formula and marked them as 1's. The issues is if at location 50 though location 58 the location percentage is the same even though the rank is increasing. I want to count those as well in the top 5% but I cant get the multiRow formula to go past one below to identify it as the same.

Datasets

Data Investigation

Accepted answers

ggruccio

Got it!

I assume records i and j would then be combined in the next group of records to make another group of 5 and similar logic would be applied below so that if records below matched the same value they would be grouped with that 5 as well.

I've created an iterative macro that accomplishes this (see the attached file with macro embedded)

This appears to match the output you are looking for:

Iterative Ranking.yxzp

All comments

ggruccio

HI @CScott,

I would try doing a Summarize - Group By or a Unique on the field you want to calculate the rankings on...then run your ranking logic, then join the ranks back to the original dataset.

For example, you have a 1000 row sample with maybe only 800 unique values. Complete the ranking on the 800 unique values, then join back to your 1000 row sample.

Will this give you the output you are looking for?

CScott

not sure that is what I was looking for. here is a sample look of the data. what i want is to meet what is in the Wanted Rank column where the ranking of 1's extends down farther due to the percentage being the same. So in this example below, top 5 in Orig Rank shows where it would end at just 5 but the issue is that the Pct is the same at 10% for others beyond and should be included as a rank of 1 also.

Location	Rating	Pct	Orig Rank	wanted rank
A	20	5%	1	1	top 5
b	20	5%	1	1	top 5
c	20	5%	1	1	top 5
d	20	10%	1	1	top 5
e	20	10%	1	1	top 5
f	30	10%	2	1	Extended for dup %
g	30	10%	2	1	Extended for dup %
h	30	10%	2	1	Extended for dup %
i	30	15%	2	2	move to next 5
j	30	20%	2	2	move to next 5

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703