I have an issue where I'm trying to do RankIfs and Rank while have both return unique ranks instead of duplicates, and all this while not sorting my data. I'll provide a sample of what I'm currently getting below. I would love to solve this in anyway possible, either VBA or Formula's. However I have many data sheets currently utilizing Sumproducts and I'm currently trying to avoid using that function as it's extremely taxing.
Category | Total Value | Rank | Unique ID
lkj | 494.1 | 2 | lkj2
asdf | 679.6 | 1 | asdf1
lkj | 494.1 | 2 | lkj2
tdd | 110.5 | 2 | tdd2
tdd | 120.5 | 1 | tdd1
lkj | 800.5 | 1 | lkj1
A B C D
1 Category | Total Value | Rank | Unique ID
2 FX | 494.1 | 2 | FX2
3 FX | 679.6 | 1 | FX1
4 FX | 494.1 | 2 | FX2
5 FX | 110.5 | 4 | FX4
So the first example is where I'm trying to do RankIfs. The total value's column is referencing a raw data sheet. I know that I can just combine the rank and total value into one column, but for example sake I thought it would be easier to explain what I'm referring to be laying everything out.
The 2nd problem is that in my 2nd table I have issues where sometimes the total values will be exactly the same, and I just need to have one of the FX's be rank 3. What I'm currently doing now is going back in and manually adjusting this, and with lots of data sheets where I have to do ranks you can imagine this can be very taxing on time. Not at all hard just a lot of manual work that I feel could be automated.
I can't for the life of me think of anyway to resolve this in either Functions or VBA. The only thing I can think of is to have everything ranked and then sorted like the example below.
A B C D
1 Category | Total Value | Rank | Unique ID
2 FX | 679.6 | 1 | FX1
3 FX | 494.1 | 2 | FX2
4 FX | 494.1 | 2 | FX2
5 FX | 110.5 | 4 | FX4
Then have a formula or vba solution where it looks at D2 and says if above cell equals D2 then give me C1 + 1, otherwise give me C1. This'll matter once it loops and get's to D4 as D3 does equal D4. This solution just seems extremely archaic to me, but if there's no better way to do it outside of this than I'll just record the macro.
Any insights are greatly appreciated.
Thanks,
If you are looking for a way to get unique values from rank the following function will do that:
=RANK(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1
Since the countif only counts values in the rows above it the first one it finds with a given value will have the higher rank.
I don't really understand what you are saying about rank if so I'm not going to try and answer that.