Excel custom sorting with custom cmp-CodePudding

Im totaly new in excel nor in VBA.

I need to write a VBA macro that will sort dogs by total gained points and if the points are same then check each atribute (from left to right) and sort by these.

I wrote some (i think) working sort in python:

import random
from functools import cmp_to_key
class Structure:
    def __init__(self,total,agility,speed,endurance,follow,enthusiasm):
        self.total = total
        self.agility = agility
        self.speed = speed
        self.endurance = endurance
        self.follow = follow
        self.enthusiasm = enthusiasm

    def __str__(self):
        return 'Structure(Total='   str(self.total)   ' ,agility='   str(self.agility)  ' ,speed='   str(self.speed)   ' ,endurance='   str(self.endurance) \
               ' ,follow='   str(self.follow) ' ,enthusiasm='   str(self.enthusiasm) ')'


def compare(item1, item2):
    if item1.total < item2.total:
        return -1
    elif item1.total > item2.total:
        return 1
    else:
        #Agility compare
        if(item1.agility>item2.agility):
            return 1
        elif(item1.agility<item2.agility):
            return -1

        #Speed compare
        if(item1.speed>item2.speed):
            return 1
        elif(item1.speed<item2.speed):
            return -1

        #Endurance compare
        if(item1.endurance>item2.endurance):
            return 1
        elif(item1.endurance<item2.endurance):
            return -1
        #Follow compare
        if(item1.follow>item2.follow):
            return 1
        elif(item1.follow<item2.follow):
            return -1
        #Enthusiasm compare
        if(item1.enthusiasm>item2.enthusiasm):
            return 1
        elif(item1.enthusiasm<item2.enthusiasm):
            return -1

        return 0


def fill():
    #total = random.randint(163,170)
    total = 170
    agility = 0
    speed = 0
    endu = 0
    fol = 0
    enth = 0
    while(total!=agility speed endu fol enth):
        agility = random.randint(20,40)
        speed = random.randint(20,40)
        endu = random.randint(20,40)
        fol = random.randint(20,40)
        enth = random.randint(20,40)
    return [total,agility,speed,endu,fol,enth]


if __name__ == "__main__" :
    list = []
    for i in range(10):
        k = fill()
        list.append(Structure(k[0],k[1],k[2],k[3],k[4],k[5]))
    for i in list:
        print(i)
    print("*********************** after sort *******************")
    zoznam = sorted(list, key=cmp_to_key(compare),reverse=True)
    for i in zoznam:
        print(i)

but i have no idea how to write it in excel.

My idea is that i select total numbers and it will sort whole row. The "data structure" in excel looks like this:

For example as you can see (on top) both of them have total of 170, agility same so pass and speed is higher so this is why he is on top.

Thanks in advance

EDIT:

Thanks a lot gimix :) Because i need more than three keys and i want only to sort selected ones i "changed" a little bit a macro to:

Selection.Sort Key1:=Range("I1"), _
                     Order1:=xlDescending, _
                     Key2:=Range("J1"), _
                     Order2:=xlDescending, _
                     Key3:=Range("K1"), _
                     Order3:=xlDescending, _
                     Header:=xlNo

Selection.Sort Key1:=Range("L1"), _
                     Order1:=xlDescending, _
                     Key2:=Range("G1"), _
                     Order2:=xlDescending, _
                     Key3:=Range("H1"), _
                     Order3:=xlDescending, _
                     Header:=xlNo

The thing is, it SEEMS like it is working but i dont know if it SHOULD be "sorted two times" like these, and if there wouldnt be some kind of "leaks" (unwanted behavior)

edit2:

Shouldnt it be rather like this ?

Selection.Sort Key1:=Range("K1"), _
                     Order1:=xlDescending, _
                     Header:=xlNo
Selection.Sort Key1:=Range("J1"), _
                     Order1:=xlDescending, _
                     Header:=xlNo
Selection.Sort Key1:=Range("I1"), _
                     Order1:=xlDescending, _
                     Header:=xlNo
Selection.Sort Key1:=Range("H1"), _
                     Order1:=xlDescending, _
                     Header:=xlNo
Selection.Sort Key1:=Range("G1"), _
                     Order1:=xlDescending, _
                     Header:=xlNo

Selection.Sort Key1:=Range("L1"), _
                     Order1:=xlDescending, _
                     Header:=xlNo

CodePudding user response：

In VBA you have the sort method of the range object:

Range("A6:L11").Sort Key1:=Range("L1"), _
                     Order1:=xlDescending, _
                     Key2:=Range("G1"), _
                     Order2:=xlDescending, _
                     Key3:=Range("H1"), _
                     Order3:=xlDescending, _
                     Header:=xlNo

Key1 etc identify which column to use; Order1 etc tell if you want to sort from lowest to highest or the other way round (default is xlAscending so you need to specify this); finally Header tells if your data has a header row (in your case we use xlNo since you have non-data rows between the headers (row1) and the data (row6 and following).

Btw your Python code could be simpler: just create a tuple of total, agility and speed and use it as the key: no need for defining a function nor for calling cmp_to_key()