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 can I sort the matrix so that each value is combined with year and depth in the same line.
I have tried to solve the problem using a Pivot table in Excel Office 2010, in vain.
The first figure shows the data matrix:
The second figure below shows the wanted solution:
In advance:
Thank you for your help
4 Replies
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,"|"))))https://learn.microsoft.com/en-us/answers/questions/5336745/transposing-one-row-into-multiple-rows?forum=msoffice-all&referrer=answers
Concept like this:
- Install Power Query Add-In
- Load Your Matrix into Power Query
- Select your matrix and go to Power Query > From Table
- Make sure your matrix has headers (e.g. years) and row labels (e.g. depts)
- Unpivot the Data
- Select the column containing depths
- Right-click and choose Unpivot Other Columns
- Dept
- Attribute (year)
- Value
- In Power Query:
- This will convert your matrix into a table with:
- Rename Columns
- Rename “Attribute” to “Year”
- Rename “Value” to “Measurement” or whatever fits your context
- Filter Out Empty Values
- Use the filter dropdown on the “Value” column to remove null or empty cells
- Load the Clean Table Back to Excel
- Click Close & Load to insert the cleaned table into a new sheet
Power Query worked well
Thank you for your help.
Kind Regards
POJ
- Install Power Query Add-In
