In Excel Crosstab, Transpose Cross Cells to Columns and Rows to Cross Cells Respectively

Problem description & analysis:

In the following Excel crosstab, the left headers are truck numbers, the headers at the top are job names, and the cross cells contain job numbers.

A   B   C   D
1   Truck Number    Job1    Job2    Job3
2   71  5928    5928    5928
3   72  3958    5928    2971
4   73  2971    5928    2971

Computing requirement: transpose unique cross cells to column names and the left headers to cross cells.

A   B   C
1   2971    3958    5928
2   72  72  71
3   73      71
4   73      71
5           72
6           73

Solution:

Use SPL XLL to enter the following formula:

=spl("=E@p(?.news(~.m(2:);~:T,get(1):S).group(T;~.(S(1)).sort():TS).(T|TS))",A2:D4)

As shown in the picture below:

Explanation:

The news()function generates multiple records according to a sequence; ~.m(2:) means getting members from the current one ~’s second sub-member to the last one; get(1) gets members on the upper layer loop. E@p() function performs transpose members of the sequence.