Mathematical transpose in excel

Question

Good day everybody! I'm currently trying to figure something out in excel before implementing in it VBScript. I have to mathematically transpose a few cells (10*10 or 5r*10c) in a matrice:

-------------------------------
| .. | .. | .. | .. | .. | .. |
| 21 | 22 | 23 | 24 | 25 | .. |
| 11 | 12 | 13 | 14 | 15 | .. |
|  1 |  2 |  3 |  4 |  5 | .. |
-------------------------------

Must become

-------------------------------
| .. | .. | .. | .. | .. | .. |
|  3 | 13 | 23 | 33 | 43 | .. |
|  2 | 12 | 22 | 32 | 42 | .. |
|  1 | 11 | 21 | 31 | 41 | .. |
-------------------------------

Now I'm not a mathematician (I'm more ore less a programmer at the moment), but I came up with: F(y)=((MOD(x,10)-1)*10)+(1+((x-MOD(x,10))/10)) (x is the value in the pre-block a the top, y is the value in the pre-block below.) Now this works fine up to a certain point (e.g. 10).

In VBScript, I wrote the below at first:

Function GetPosInSrcRack(Pos)
    Dim PlateDef(9), x, y, i, tmp

    ' Plate Definition
    ReDim tmp(UBound(PlateDef))
    For x = 0 To UBound(PlateDef)
        PlateDef(x) = tmp
    Next

    i = 1
    For x = 0 To UBound(PlateDef)
        For y = 0 To UBound(PlateDef(x))
            PlateDef(x)(y) = i
            i = (i + 1)
        Next
    Next

    'Dim msg ' Check definition
    'For x = 0 To (UBound(PlateDef))
    '    msg = Join(PlateDef(x), ", ") & vbCrLf & msg
    'Next

    ' Get the Position
    y = (pos Mod 10)
    x = ((pos - y) / 10)

    GetPosInSrcRack = PlateDef(y)(x)
End Function

Which, of course, works but is crappy.

Using the above formula I would write:

Function GetPosInSrcRack(Pos)
    Pos = (((Pos MOD 10)-1)*10)+(1+((Pos - (Pos MOD 10))/10))
End Function

But like I said, this still is incorrect (10 gives -8) Can somebody help me?

Gametangium

Answer 1

y=(MOD(x-1,10))*10+INT((x-1)/10)+1

(By the way, what you are doing is not matrix transposition, but this does do what you do, only better.)

Geller

That seems to do the trick! Thanks! It's not mathematically perfect, but neither was mine and like you said, it does do what I want! Thank you! Small edit: +1-1 is 0 so we can drop that ;-) – Gametangium

@JvN Please note that beyond 10 rows or columns you will certainly run into trouble. The simple reason is that, e.g., the number 11 appears in two places and its "transpositions" are the numbers 2 and 101. So, for larger matrices the formula you seek can't exist. – Fen

Answer 2

Just use Paste Special > Transpose option.

Ligamentous

That would be it, but I have to do it programatically somewhere else. Somewhere I dont have a range of cells, but where I need to find, for example, F(i) where i=5 (would be 41). Thanks in advance, of course! – Gametangium

And what about TRANSPOSE function? Entered as array formula with proper dimensions it will return the desired output as well. – Ligamentous

Transpose Won't do the trick either. I'm trying to figure this one out in excel like this. But afterwards I won't have an array so the Transpose functionality of Excel wont work. I do however know the amount of rows and columnns. Thanks for the comments! – Gametangium