Search code examples
excelranking

Creating a rank/index based on multiple columns

I wish to create a ranking/index in excel using multiple columns. I have tried rank and sumproduct, but I cannot achieve the desired result. I have three columns (one being a date) and would like to create the following RANK;

Type    WO Ref  Begin Prod  RANK
510     00513   15/11/2018  1
510     00517   16/11/2018  2
500     04678   11/12/2018  1
500     04685   19/12/2018  2
500     05836   29/12/2018  3
500     05837   29/12/2018  4
510     00523   14/12/2018  1
510     00524   15/12/2018  2
510     04797   15/12/2018  3
500     00374   04/01/2019  1
500     05090   05/01/2019  2
500     05091   06/01/2019  3
510     05830   27/01/2019  1
510     05831   27/01/2019  2

Ranking should be in in the following order;

  1. Year/Month (from 'Begin Prod' - Column J)
  2. 'Type' (Column C)
  3. 'Begin Prod' (Column J)
  4. 'WO Ref' (Column D)

I started using the following nested sumproduct to rank the year/month and 'Type'...

=SUMPRODUCT(--(TEXT(J$2:J2,"yymm")=TEXT(J2,"yymm")),--((C$2:C2)=C2))

... but as soon as I add the 'Begin Prod' date the ranking changes to 1 and 2 only?

Solution

  • just a quick thought: You could introduce a new column and use the concatenate function (https://support.office.com/en-us/article/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d)

    Syntax: CONCATENATE(text1, [text2], ...).

    And then sort by this column. You can hide the column if its bothering you after sorting.