Results 1 to 8 of 8

Rank based on multiple columns

This is a discussion on Rank based on multiple columns within the Excel Questions forums, part of the Question Forums category; Hello, I have three columns in a datasheet as follows: Column A: Disease Column B: Hospital Column C: Grams_Administered Column ...

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Posts
    97

    Default Rank based on multiple columns

    Hello, I have three columns in a datasheet as follows:

    Column A: Disease
    Column B: Hospital
    Column C: Grams_Administered
    Column D: Rank

    Basically , I need to rank each row (disease) respectively within its hospital based on the Grams_Administered. In the rank column, therefore, there will be multiple entries with the same rank because there are multiple hospitals. Any ideas?

    Thanks,

    J
    Last edited by jrudner; Sep 24th, 2008 at 08:54 AM.

  2. #2
    Board Regular ianjohnwalker's Avatar
    Join Date
    Jan 2008
    Location
    Southampton, UK
    Posts
    65

    Default Re: Rank based on multiple columns

    Hi J,

    Just to clarify, you need to know the totals for each hospital, which diseases have the most grams?

    I don't think you can specify criteria in rank, so I suggest creating a separate table PER HOSPITAL, then adding a list of the diseases in the same column. Then use SUMPRODUCT to calculate the totals, then add a rank column and hey presto, not ideal but it will be difficult to do this any other way I think.

    Something like...

    ColA...............ColB..................................ColC.....
    Disease..........Hospital A...........................Rank
    DisA..............=sumproduct((etc.etc.....))....=rank(etc.etc.

    Ian
    Ian Walker
    Bright Idea Consulting Ltd

  3. #3
    Board Regular ianjohnwalker's Avatar
    Join Date
    Jan 2008
    Location
    Southampton, UK
    Posts
    65

    Default Re: Rank based on multiple columns

    Obviously you could also carry this same pattern to ColD ColE etc. for other hospitals
    Ian Walker
    Bright Idea Consulting Ltd

  4. #4
    Board Regular
    Join Date
    Jul 2008
    Posts
    97

    Default Re: Rank based on multiple columns

    Do you know of a formula that I could use? I did this exact thing once using the * (asterisk) operator and it worked like a charm. Sadly I can't recall the formula

  5. #5
    Board Regular ianjohnwalker's Avatar
    Join Date
    Jan 2008
    Location
    Southampton, UK
    Posts
    65

    Default Re: Rank based on multiple columns



    nope, sorry
    Ian Walker
    Bright Idea Consulting Ltd

  6. #6
    Board Regular
    Join Date
    Jul 2008
    Location
    Surrey, UK
    Posts
    1,473

    Default Re: Rank based on multiple columns

    The only approach that I can think of is a series of helper columns - two for each hospital - one for the value, and the other for the rank. You will then need an additional pair of columns to give you the rank overall. you can avoid repeated rankings by using :-

    RANK(A1,A1:A100) + COUNTIF($A1:A1) - 1 in the overall rank column

    Thanks

    Kaps
    Read my Excel blog on

    http://simplyspreadsheets.wordpress.com/

    For more ways I can help you with Excel :-

    www.simplyspreadsheets.co.uk

  7. #7
    Board Regular
    Join Date
    Jul 2008
    Posts
    97

    Default Re: Rank based on multiple columns

    Found it!

    =SUMPRODUCT(($B$2:$B$1030=B2)*(C2<$C$2:$C$1030))+1

    The name of the disease doesn't matter, just the total grams of it within the hospital.

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    7,648

    Default Re: Rank based on multiple columns

    Hi, Although you seem to have a solution, you could try this.
    Data based on your first post.
    Results by Hospital then Greatest Grams per disease.
    Results in cell "F2" on.
    Nb:- This is all one code !
    Code:
    Dim rCells As Range, rCell As Range, oHosp As Variant, c, nCell As String
    Dim oDis As Variant, oH As Integer, oD As Integer
    Set rCells = Range("B2", Range("B" & Rows.Count).End(xlUp))
     Dim a
     
     With CreateObject("scripting.dictionary")
        For Each rCell In rCells
                    If rCell <> "" Then
                If Not .Exists(rCell.Value) Then
                    .Add rCell.Value, rCells
                    c = c + 1
                 End If
            End If
        Next rCell
        oHosp = .keys
    End With
    '----------
    Set rCells = Range("a2", Range("a" & Rows.Count).End(xlUp))
    
    c = 0
     
     With CreateObject("scripting.dictionary")
        For Each rCell In rCells
                    If rCell <> "" Then
                If Not .Exists(rCell.Value) Then
                    .Add rCell.Value, rCells
                    c = c + 1
                 End If
            End If
        Next rCell
        oDis = .keys
    End With
     
    
    '==================
    
    Dim sortArray, i As Long, j As Long, temp(1 To 4) As Variant
    Dim Rng As Range
    ReDim sortArray(1 To UBound(oHosp) + 1, 1 To 4)
    
    For oH = 0 To UBound(oHosp)
    c = 0
    For oD = 0 To UBound(oDis)
    For Each rCell In rCells
    
    If rCell.Offset(, 1) = oHosp(oH) And rCell = oDis(oD) Then
    c = c + 1
    
    sortArray(c, 1) = rCell.Offset(, 1)
        sortArray(c, 2) = rCell
            sortArray(c, 3) = rCell.Offset(, 2)
            sortArray(c, 4) = c 
    End If
    Next rCell
    Next oD
    
    Dim Last As Integer
    
    
    For i = 1 To (UBound(sortArray) - 1)
       For j = i To UBound(sortArray)
    
          If sortArray(j, 3) > sortArray(i, 3) Then
             
             temp(1) = sortArray(i, 1)
                temp(2) = sortArray(i, 2)
                    temp(3) = sortArray(i, 3)
             
             sortArray(i, 1) = sortArray(j, 1)
                sortArray(i, 2) = sortArray(j, 2)
                    sortArray(i, 3) = sortArray(j, 3)
            
             sortArray(j, 1) = temp(1)
                sortArray(j, 2) = temp(2)
                    sortArray(j, 3) = temp(3)
         
          End If
       Next j
    Next i
    Last = Range("f" & Rows.Count).End(xlUp).Row + 1
    Range("f" & Last).Resize(c, 4).Value = sortArray
    
    Next oH
    Regards Mick

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com