1

I have multiple lists of differing lengths containing items that may or may not appear in all lists. The higher an item appears in a list the better it's ranking. I've been trying to find a way of ranking the items from the combined result of all lists. Here's an example

List 1   list 2   list 3
bob      jane     fred
fred     peter    jane
alan     fred     bob
steve             brian
                  julie

I thought that a ranking column might help to give values so it would be something like:

Rankvalue  List 1   list 2   list 3
100        bob      jane     fred
80         fred     peter    jane
70         alan     fred     bob
60         steve             brian 
50                           julie

Then Bob would have 100 + 70 = 170, Fred would have 100 + 80 + 70 = 250, (beating Bob and topping the list)

Is there some function in excel that allows me to to this? I was looking at Vlookups and Ranks but nothing seems to hit the mark. I imagine this is quite common but I've been looking around and can't find an answer. Note: I'd be happy to write this function in ANY language, but excel seemed to be the most straightforward way of organising lists. If anyone has a solution in Ruby, Python, etc I'd gladly look over it. thanks!

boogiewonder
  • 145
  • 1
  • 9
  • 2
    I don't have time to give a real answer, but look at the `MATCH` function in Excel. That finds the position of an item in a list. then you can just sum up the positions found for each name. Assuming stuff like names only appear once per list, your sum of ranks is the metric you want, etc. – jtolle Dec 04 '15 at 17:04
  • See if you can come up with anything involving a [SUMIF](https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B) or [SUMIFS](https://support.office.com/en-us/article/SUMIFS-function-9DD6179E-CCED-41DD-AC38-08FDF5B929E5) function. –  Dec 04 '15 at 17:04
  • Can you define rank in this situation? if in the 3 lists something is placed 1 , 1, 7 and something else is placed 3, 3, 3 are you saying they are equal?? – Steven Martin Dec 04 '15 at 18:50

3 Answers3

2

I'd restructure the data a bit, then it's easy to use a VLOOKUP and a PivotTable.

  1. Put your lists beneath each other, in order like in my screenshot (cells A12:24).
  2. In column C enter a formula like this to calculate the order of each distinct list: =IF(A13<>A12,1,C12+1)
  3. Create a RankValue reference table (G1:H6 in my screenshot), and then in column D of your new table, enter this formula to get the value: =VLOOKUP(C13,$G$1:$H$6,2,0)
  4. Use a PivotTable to sum the total RankValue.

The benefit of this approach is you don't have to identify the list of names you want to calculate values for - the PivotTable does it for you.

enter image description here

Andi Mohr
  • 458
  • 1
  • 6
  • 22
  • Thanks @andimohr that's pretty much in the direction I want to go. I worked through your suggestions and I get the pivot table with rankings and I don't need to know the names beforehand, which removes an extra step. I'm trying to automate this process as much as possible. Basically, I want to pull in lists (of random length and items) and then let excel create the rankings as in the pivot table. I'll keep working on it but you've helped that process a lot ! – boogiewonder Dec 04 '15 at 18:03
2

You could use Index() Match:

enter image description here

The formula in the above is:

=IFERROR(INDEX(A:A,MATCH(F2,B:B,0)),0)+IFERROR(INDEX(A:A,MATCH(F2,C:C,0)),0)+IFERROR(INDEX(A:A,MATCH(F2,D:D,0)),0)

A shorter formula:

=SUMIF(B:B,F2,A:A)+SUMIF(C:C,F2,A:A)+SUMIF(D:D,F2,A:A)

To do everything automatically you will need to use vba.

Sub boogie()
Dim lstrow&

With ActiveSheet
    ' add a title
    .Range("F1") = "List"
    .Range("G1") = "Rank"

    'Loop through columns of data and paste into one column
    For i = 2 To 4
        .Range(.Cells(2, i), .Cells(2, i).End(xlDown)).Copy .Cells(.Rows.Count, "F").End(xlUp).Offset(1)
    Next i

    'Remove Duplicates
    lstrow = .Range("F1").End(xlDown).Row
    With .Range("F1:F" & lstrow)
        .Value = .Value
        .RemoveDuplicates Columns:=1, Header:=xlYes
    End With

    'Add formula and copy down
    lstrow = .Range("F1").End(xlDown).Row
    .Range("G2").Formula = _
         "=SUMIF(B:B,F2,A:A)+SUMIF(C:C,F2,A:A)+SUMIF(D:D,F2,A:A)"
    .Range("G2:G" & lstrow).FillDown
    .Calculate

    'sort
    Columns("F:G").Sort key1:=Range("G2"), _
      order1:=xlDescending, Header:=xlYes
End With

End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    @pnuts high praise indeed, I feel honored. :). – Scott Craner Dec 04 '15 at 17:21
  • Many thanks @ScottCraner ! That's a great solution but I don't think I can make it work. Maybe I didn't explain it very well. As the lists will contain 'random' items, essentially unknown before pasting in the spreadsheet, I'd need to know what items appear and then manually enter them in column F as you suggested. I think that's how it work work above, right? Going on pnuts recommendation I guess I can compile the lists into one and then use the single list in column F. Does that sound feasible? I've tried to do that but what I wanted to do was just drop lists into the sheet and automate it – boogiewonder Dec 04 '15 at 17:39
  • @boogiewonder give me a little while and I will walk step by step through what pnuts is saying. I am in a meeting at the moment and away from my desk. – Scott Craner Dec 04 '15 at 17:41
  • @boogiewonder see edit, the only way to automate is with vba. – Scott Craner Dec 04 '15 at 18:15
  • @ScottCraner I'm getting 'Object required' on that VBA macro. I tried stepping through to see what that means but there's nothing to go on. Do you know what it might be? – boogiewonder Dec 04 '15 at 20:41
  • @boogiewonder found it, that's what I get when I edit the code on site without trying it in excel. See edit. – Scott Craner Dec 04 '15 at 20:50
  • @ScottCraner Superb!! that works perfect. You've probably saved me days of my life trying to figure that out. I think I understand how it works also so it's a good one to study. Much appreciated. – boogiewonder Dec 04 '15 at 21:01
0

As much for the sake of application of SUMIF here, an alternative that might be worked through with Record Macro is to 'unpivot' as detailed here and extend the resulting table with, in D2: 

 =SUMIF(C:C,C2,A:A)

Sort ColumnD Largest to Smallest, Copy, Paste Special..., Values over the top, Convert to Range and then Remove Duplicates based on Value column.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139