Forum Discussion
Extracting data from a matrix
I have a matrix containing 4348 values distributed in a matrix Most of the cells in the matrix contians no values (are empty) Some cells contains values which I want to extract. The problem is how...
For Excel-2010 it will need few of a helper column or VBA or Power Query approach. For Microsoft Excel 365 here is a formula approach.
=REDUCE({"Year","Depth","Value"},TOCOL(B1:D1&"|"&A2:A4&"|"&B2:D4),LAMBDA(a,x,VSTACK(a,TEXTSPLIT(x,"|"))))