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