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.

Comments

[Judith-Excel-Sharing]

Got any complicated Excel assignments? Our community is filled with Excel experts ready to offer a helping hand. Get solutions and guidance on your toughest Excel problems: discord.gg/hgbKEvJ4 We'll see you there!🙌🏻

[Judith-Excel-Sharing]

SPL XLL always offers the easiest and most effective solutions! Do you have any other thoughts on the solution, please do not hesitate to share them. You can also experience the power of SPL XLL by just clicking: scudata.com/download-Desktop