Forum Discussion

Greg_Taylor's avatar
Greg_Taylor
Copper Contributor
Dec 08, 2023

Searching a complex set of data

I have an Excel File generated from a debase report. Each "Record" in the database fills 6 rows by 15 columns. I want to sort the "records" by cell Row3 Column N. Any idea how to do that?
BI & Data Analysis
Excel on Mac
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 09, 2023

Greg_Taylor 

The key feature of the formulas is that they are written for Excel 365.  Array formulas are used so that the entire table of data is returned in the required sort order from a single cell and spills out down and to the right.

 

This is very different from traditional spreadsheet methods in which a single cell is calculated using scalar arithmetic and the formula is copied down and to the right using relative referencing.

 

This final version uses a Lambda function which collects the references together as a parameter string and means you do not have to look at the formulas, just the results.

= BlockSortĪ»(data, 6, 3, 14)

= LAMBDA(data, blockHeight, keyRow, keyColumn,
    LET(
      blockCount,  QUOTIENT(ROWS(data), blockHeight),
      keyRows,     SEQUENCE(blockCount, 1, keyRow, blockHeight),
      keyValue,    INDEX(data, keyRows, keyColumn),
      blockedKey,  TOCOL(keyValue + SEQUENCE(1, blockHeight, 0, 0)),
      SORTBY(data, blockedKey)
    )
  )

You may observe that the whole formula looks more like a code module than a traditional spreadsheet formula and this version is a function stored by name in Name Manager.

 

If you cannot access 365, similar results may be available to you using PowerQuery.

Resources