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.