Forum Discussion

Vaibhav002's avatar
Vaibhav002
Copper Contributor
Sep 11, 2023

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

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 kn...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 11, 2023

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
  )

 

Resources