ABCDEFGHIJKLMNOP
1
This sheet gives single cell formula to Unpivot or Reverse-Pivot a matrix by using newly introduced fature Named-function, BYROW and FLATTEN function. It can take any number of fixed columns. This is the shortest possible Unpivot function. The sheet is in public domain and the function can be imported. It can handle even more than one fixed columns. Quick link to the sheet is http://tiny.cc/unpivot
2
By S K Srivastava
3
Quick link to this sheet
http://tiny.cc/unpivot
4
5
Col1Col2Col3Col4=UNPIVOT(A6:D13,E5:H5,E6:H13)
6
Field1Field2Field3Row1150201746232482Field1Field2Field3Row1Col115020
7
Field4Field5Field6Row266001320019800Field1Field2Field3Row1Col2
8
Field7Field8Field9Row32784020003972069560Field1Field2Field3Row1Col317462
9
Field10Field11Field12Row444402666431104Field1Field2Field3Row1Col432482
10
Field13Field14Field15Row51550244403333053272Field4Field5Field6Row2Col16600
11
Field16Field17Field18Row6368851418057156Field4Field5Field6Row2Col2
12
Field19Field20Field21Row73333033330Field4Field5Field6Row2Col313200
13
Field22Field23Field24Row8129622666439626Field4Field5Field6Row2Col419800
14
Field7Field8Field9Row3Col127840
15
Field7Field8Field9Row3Col22000
16
Field7Field8Field9Row3Col339720
17
Field7Field8Field9Row3Col469560
18
Field10Field11Field12Row4Col14440
19
Field10Field11Field12Row4Col2
20
Field10Field11Field12Row4Col326664
21
Field10Field11Field12Row4Col431104
22
Field13Field14Field15Row5Col115502
23
Field13Field14Field15Row5Col24440
24
Field13Field14Field15Row5Col333330
25
Field13Field14Field15Row5Col453272
26
Field16Field17Field18Row6Col136885
27
Field16Field17Field18Row6Col214180
28
Field16Field17Field18Row6Col357156
29
Field16Field17Field18Row6Col4
30
Field19Field20Field21Row7Col1
31
Field19Field20Field21Row7Col2
32
Field19Field20Field21Row7Col333330
33
Field19Field20Field21Row7Col433330
34
Field22Field23Field24Row8Col112962
35
Field22Field23Field24Row8Col2
36
Field22Field23Field24Row8Col326664
37
Field22Field23Field24Row8Col439626
38
39
40
41