Sep 10 2023 11:39 PM - edited Sep 11 2023 01:03 AM
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).
Sep 11 2023 01:44 AM
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.
Sep 11 2023 12:18 PM
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
)