excelexcel-formularanking

Ranking number and letter columns in Excel (with a twist)

I have storage system, that includes multiple trays each having storage boxes in them. One tray have always one sized boxes in it, but different trays may have different sizes. So one tray may have 5x10 boxes in it, while another have 3x6 or so...

The management system has a strange way of indicating the box's position in the tray. Instead of indicating X/Y-positions, it will use locations so, that each tray is divided into numbers (X-axis) and letters (Y-axis) and even so, that numbers and letters are spread over the whole tray area.

So thinking about tray with 6x3 (X x Y) boxes. the bottom left box is 1A, but the next above it may be 1F and the third 1K and the next "column" might be 7A, 7F and 7K. So in case of very small boxes the order may come to 1A,1B,1C.... 2A,2B,2C... etc, but with bigger boxes both numbers and letters skips values in between. However they are always in order both numbers from smallest to largest and letters in alphabetical order...

What I would need is some Excel magic, where that "numbers/letters-skipping" order would be transformed as numerical X/Y positions. So in above example 1A,1F,1K, 7A,7F,7K would be converted to 1/1, 1/2, 1/3, 2/1, 2/2, 2/3 (in two columns).

So, my sample excel has 3 columns Tray, LocX, LocY and I have added values to columns 4 and 5 (X-pos / Y-pos, respectively), what they should be. These columns 4 and 5 should have the formulas performing the task in hand.

Also the Excel has data of multiple trays, so the formula should take care of "Tray" column so, that it would rank those numbers/letters into columns within that tray, so all trays have X/Y-rank 1/1 in bottom left corner.

My Excel has thousands of lines, so selecting small array at the time (as e.g. standard RANK-function wants) is not a solution, but whatever formula is in cell D2 (or E2) should be expandable to the whole column...

Excel looks like this

Can you actually add Excel-file somehow here?

Here is copy-pasteable version of the table:

Tray LocX LocY X-pos Y-pos
1 1 A 1 1
1 1 F 1 2
1 1 J 1 3
1 1 O 1 4
1 4 A 2 1
1 4 F 2 2
1 4 J 2 3
1 4 O 2 4
1 7 A 3 1
1 7 F 3 2
1 7 J 3 3
1 7 O 3 4
1 10 A 4 1
1 10 F 4 2
1 10 J 4 3
1 10 O 4 4
1 13 A 5 1
1 13 F 5 2
1 13 J 5 3
1 13 O 5 4
2 1 A 1 1
2 1 C 1 2
2 1 E 1 3
2 2 A 2 1
2 2 C 2 2
2 2 E 2 3
2 3 A 3 1
2 3 C 3 2
2 3 E 3 3
2 4 A 4 1
2 4 C 4 2
2 4 E 4 3
3 1 A 1 1
3 1 J 1 2
3 15 A 2 1
3 15 J 2 2
3 29 A 3 1
3 29 J 3 2

Tried RANK , RANK.EQ and some COUNTIFS , but could not fix this

Solution

  • enter image description here

    Formula for X-pos:

    =IF(A2<>A1;1;IF(B2<>B1;1+D1;D1))

    Formula for Y-Pos:

    =IF(D2<>D1;1;E1+1)

    These formulas will return your expected output as long as your data input is sorted as you've provided.