How To Flatten and Filter a Matrix in Microsoft Excel

Опубликовано: 31 Октябрь 2024
на канале: Andrew Magee

353

This video walks through how to take a matrix of data in excel, flatten it out into rows, and then perform a filter on it to obtain meaningful insights from the matrix of data. Chapters and formulas are in the description below.

Chapters:
0:00 Overview
2:13 Formulas
2:49 Setting Up Matrix
3:38 TOCOL and TOROW functions
5:30 Flattening Data: Creating Column and Row # Columns
14:10 Flattening Data: Obtaining Values Using Index-Match
17:06 Flattening Data: Obtaining Column and Row Name Using XLOOKUP
19:34 Updating Matrix with New Information
20:37 Filtering Flattened Data using FILTER() Function
28:59 Using Nested Filter to hide specific columns from filter results.

Formulas:
Column # Column:
=LET(
columnNumbers,COUNTA($AV$3:$JU$3),
rowNumbers,COUNTA($AT$6:$AT$1000),
totalCells,columnNumbers*rowNumbers,
MOD(SEQUENCE(totalCells,1,0),columnNumbers)+1)

Row # Column:
=LET(
columnNumbers,COUNTA($AV$3:$JU$3),
rowNumbers,COUNTA($AT$6:$AT$1000),
totalCells,columnNumbers*rowNumbers,
ROUNDUP(SEQUENCE(totalCells,1,1)/columnNumbers,0))

Cell Value Column:
=LET(
columnNumbers,COUNTA($AV$3:$JU$3),
rowNumbers,COUNTA($AT$6:$AT$1000),
matrixArray,$AV$6:OFFSET($AV$6,rowNumbers-1,columnNumbers-1),
INDEX(matrixArray,V4#,U4#))

Obtaining Row Name:
=LET(
rowNumber,V4#,
rowNumberColumn,$AT$6:$AT$1000,
rowTitleColumn,$AU$6:$AU$1000,
XLOOKUP(rowNumber,rowNumberColumn,rowTitleColumn,""))

Obtaining Column Name:
=LET(
columnNumber,U4#,
columnNumberRow,$AV$3:$JU$3,
columnTitleRow,$AV$4:$JU$4,
XLOOKUP(columnNumber,columnNumberRow,columnTitleRow,""))

Basic Filter:
=FILTER(
W4#:AC4#,
(W4#=$C$9) * (IF($C$10="All Players",TRUE,Y4#=$C$10)))

Nested Filter:
=FILTER(basic filter code from above, {0,0,1,1,1})