Getting ALL Top 1 in RANK

Mstest / Awrdacpi
May 5, 2015 at 23:24:04
Specs: Windows XP, 2 GHz / 958 MB

I have a data where in I get the top 1 based in RANK. I am okay if there is only 1 top1 and the problem is if the top1 is more than 2.

Rank        Category          Count
  4          NonPO              1
  1          Vendor             10
  3          PO                 5
  1          Invoice            10
  2          No information      8

Since I have two rank 1, the output should be "The Highest Count is Vendor and Invoice."

How can I write this in formula? For the single top 1 rank I use vlookup to get the top 1 but i do not know how if there are more than 2 Top1.

Please help.


message edited by shieldbreakers

See More: Getting ALL Top 1 in RANK

Report •

May 6, 2015 at 08:08:04
I do not belive that you can use a formula to accomplish your goal. A formula cannot "loop" through a column and build a variable length string depending on whether it finds one Top Ranked value or two or possibly even five.

Perhaps there may be an extremely complicated Nested IF/Nested VLOOKUP formula that accounts for every possible scenario, but I wouldn't even attempt to construct it.

One formula based option would be to use a technique that allows you to "break" ties by using a "Helper Column" to add a very small random number to each value, basically eliminating the possibility that there will ever be 2 values that are tied for first. If you are interested in that solution, let me know.

Otherwise, the other option would be a macro which could easily build the random length string for any number of Top Ranked ties. I think we could come up with a User Defined Function (UDF) that could be used in a cell, but that would still be a macro based solution.

Let me know if you are interested in a macro based solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 7, 2015 at 00:20:49
Hi DerbyDad03,

I am interested with the macro. Actually I found something on the one you mentioned about the Helper Column. I am considering this option but I would definitely want to know how the macro works..

Thank you in advance.

Report •

Related Solutions

Ask Question