Rearranging value in an excel table (Transpose is not working for the desired output)

[Vaibhav002]

Hi, 

I have a set of LARGE data in excel table and I am looking to rearrange the data for the same table.I have attached the sample input table and re-arranged output in the file.Please let me know on how to resolve this (transpose is not giving the right result).

[#2]

@Vaibhav002 

It should be possible to achieve this with TOCOL and WRAPROWS alone but thinking it through is painful.  I have used the fact that the array can conveniently be divided into two, rearranged and, finally, reassembled.

= LET(
    n, COLUMNS(array) / 2,
    array1, TAKE(array,,n),
    array2, DROP(array,,n),
    VSTACK(TOROW(array1), TOROW(array2))
  )

A more 'nuts and bolts' approach would to calculate indices and then put the result together with MAKEARRAY and INDEX.  

[#3]

@Vaibhav002 

I have put a version of the formula together that will allow the count of groups, ranks and tests to be changed easily. It does require 365 though.  The formula is not as straightforward as I had hoped for though.

= LET(
    groupCount,  2,
    rankCount,   3,
    testCount,   12,
    listCols,    TOCOL(array,,1),
    shapedArray, WRAPROWS(listCols, testCount * rankCount),
    sortArray,   1 + MOD(SEQUENCE(1, 36, 0), testCount),
    SORTBY(shapedArray, sortArray)
  )

I also tried a different strategy using a Lambda function 

= Pivotλ(array, 2, 3, 12)

"where Pivotλ(array, groupCount, rankCount, testCount) is given by"

= DROP(
    REDUCE("", SEQUENCE(groupCount),
        LAMBDA(acc, k,
            LET(
                block, TAKE(DROP(array, , rankCount * (k - 1)), , rankCount),
                VSTACK(acc, TOROW(block))
            )
        )
    ),
    1
  )