0

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,

new2hacking
  • 7
  • 5

1 Answers1

0

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.

Source

I don't really understand what you are saying about rank if so I'm not going to try and answer that.

gtwebb
  • 2,981
  • 3
  • 13
  • 22
  • Hey Gtwebb,Thanks for the quick response! Unfortunately that answer doesn't help though as I've tried it. one of the solutions I found was your suggestion with a slight modification on the countif, but I tried it your way as well, =RANK(X7,$X$7:$X$533)+COUNTIF(X$7:X7,X7)-1 . Ultimately the issue I ran into was that I had a rank of 103, and there was a duplicate for rank 102, but unfortunately with that formula rank 102's duplicate was taken care of, but now I have 2 rank 103's. I hope that makes sense. – new2hacking Jun 28 '14 at 02:06
  • Hah! So I found out why it didn't work. Looks like the data source just needed the numbers to be rounded. In regards to the Rank Ifs, I just meant that for I have tags associated that I need ranked. In example number one I screwed up there should be 2 lkj's and no jk. I completely updated example one, and it should be more thorough now. Sorry for the confusion, I'm actually at home sick, and wanted to get a few things done. – new2hacking Jun 28 '14 at 03:39
  • Hey Pnuts, you're sort of right. It was an issue where one of the values was reading as 15.6 and so was another. However if you formatted the number to include enough zeroes to the right I think eventually there would have been something that would have differentiated between the two 15.6 values. I didn't take the time to do this as after 10+ zeroes to the right I figured that Excel just knew, and rounded the numbers. After that it worked fine. Any insights on how to Rank based on multiple criteria's? – new2hacking Jun 28 '14 at 22:08