title: Rank(ifs) in Excel without Sumproduct | VBA vs Functions
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <pre><code>    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
  </code></pre>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>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.</p>
  
  <p>Any insights are greatly appreciated.</p>
  
  <p>Thanks,</p>