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?

score 2 · Answer 1 · answered Feb 14 '13 at 10:48

2

Just use Paste Special > Transpose option.

answered Feb 14 '13 at 10:48

Peter L.

7,276
5
34
53

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! – van Nijnatten Feb 14 '13 at 10:51
And what about `TRANSPOSE` function? Entered as array formula with proper dimensions it will return the desired output as well. – Peter L. Feb 14 '13 at 10:58
`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! – van Nijnatten Feb 14 '13 at 11:47

score 1 · Accepted Answer · edited Feb 14 '13 at 11:59

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.)

edited Feb 14 '13 at 11:59

van Nijnatten

404
5
15

answered Feb 14 '13 at 11:24

Řídící

248
1
9

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 ;-) – van Nijnatten Feb 14 '13 at 11:54
@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. – Řídící Feb 15 '13 at 09:44

Mathematical transpose in excel

2 Answers2

Linked