Transform matrix data ("Pivot Table like data") to simple dataset

[Ivan]

With help of Pivot Table Wizard it is very easy to transform a table of data
in the form of dataset into the matrix form of pivot table! Coying using
"special-values" it is also easy to get the matrix form data independent of
underlaying source data.

What about inverse (backward) of such transformation? I know that some times
(years) ago I saw a certain excel expert web page with the explanation of a
pretty simple action (not using VBA and also without a lot of copying and
inserting) you must perform to get a dataset from the matrix data.

For the clearness, from the data in the form:

Begin ----------------------------------------
colhead1 colhead2 colhead3 ...
rowhead1 value11 value12 value13 ...
rowhead2 value21 value22 value23 ...
rowhead3 value31 value32 value33 ...
.... ... ... ... ...
End ------------------------------------------

I would like to get the dataset:

Begin -----------------------
rowhead1 colhead1 value11
rowhead1 colhead2 value12
...........................................
rowhead2 colhead1 value21
rowhead2 colhead2 value22
............................................
End --------------------------

Did anybody see and remember this handling (or web page)? And is it suitable
also for richer selection of column and row headers?

Ivan

 

[Dave Peterson]

It looks like you could use John Walkenbach's "reverse pivot table" technique:
http://www.j-walk.com/ss/excel/usertips/tip068.htm

 

[Ivan]

Yes, I remember this page. Thaks Dave!

Ivan

It looks like you could use John Walkenbach's "reverse pivot table"
technique:
http://www.j-walk.com/ss/excel/usertips/tip068.htm