...

In Excel crosstab, transpose cross cells to columns and rows to cross cells

 

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

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)

Picture1png

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.

Source:https://stackoverflow.com/questions/78348967/need-to-lookup-value-in-multiple-columns-return-value-in-another-column-each-ti


 
 •  135 View  •