Sort by three columns, using formulas

Hello everyone,

I'm working on an excel table that handles electric cable types. The general structure of a cable type looks like: "cable name" "number of wires"x"wire cross section"mm2. For example, such a cable type looks like: NYY_J 4x240mm2. In my excel the list of cables need to be handled dynamically, because they are given as inputs, using drop down menus. And also, the number of cables in the list can vary. The task I am trying to achieve is that I want these cable types sorted. The first level of the sorting should base on the cable type, then the second on the number of wires, and lastly on the wire cross section. For example such a sorted list should look like: NYY_J 4x25mm2, NYY_J 4x240mm2, YSLY 3x1,5mm2, YSLY 4x1,5mm2, YSLY 4x50mm2. (Note: sorting only the whole strings do not work, because they are handled as texts, therefore 240 comes before 25)

I managed to seperate the three parts of the cable type. The cable name is kept as text, and the number of wires and wire cross sections are converted to numbers, all in three different columns. I have successfully sorted the cable names alphabetically, then I also managed to sort the number of wires. Unfortunately I can't figure it out, how should I sort the last column (wire cross section) based on the two previous ones.

I have attached the .xls file I'm working on. The headers are in Hungarian (sorry for that). The part, which I have trouble with is located on the worksheet called "Kigyűjtések", from C18, to K32. It's important, that I'm trying to make this table Excel 2003 compatible, so I'm only using functions and formulas that are available in 2003. In column D the duplicates are filtered out from the original list, then in column E, F and G the three parts of the cable type are separated. In column "I" the cable names are sorted, and in column J the number of wires are also sorted, based on the cable name. I would like to fill the column K with the sorted wire cross sections, based on the previous two columns. Then I would merge back the three components into one in the column L (this would be the easy part).

I would be really-really grateful if you could help me with this problem. Any other suggestions, or a completely another approach that leads to the cable types being sorted the way I described are also welcome.

Thank you for your reply!

You probably misunderstood, or I wasn't completely clear about my issue. The table I attached previously have already contained these columns that separate the three parts of the cable type. I was having trouble to sort them with the use of formulas. I mean I wanted to handle everything dynamically, because any time a new cable can added, or an already existing can be modified. In the previously attached table, I managed to work out the first two levels of the sorting, the only trouble was with the third level.

Anyway, I've decided to move on (and it will be time for my colleagues, too ), and upgraded to Excel 2010. That is, because now I can use the COUNTIFS function, which makes the sorting so much easier. So I'd just like to share the working solution. Firstly the COUNTIFS functions are used to determine the ranks of cable types (all three components ranked in a singe formula):
=COUNTIF($E$18:$E$167,"<"&E18)+COUNTIFS($E$18:$E$167,$E18,$F$18:$F$167,"<"&$F18)+COUNTIFS($E$18:$E$167,$E18,$F$18:$F$167,$F18;$G$18:$G$167,"<"&$G18)+1
Then simply INDEX and MATCH functions are used to sort the cables, based on their ranks:
=INDEX($D$18:$D$167,MATCH(ROWS($I$18:I18),$H$18:$H$167,0))

So I managed to work around my problem. But if anyone had a solution that also works in Excel 2003, I would still be interested. But anyway, thank you for your help.

Thank you very much for your help. Not everything is clear yet, but I'm trying to understand the idea.