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 08, 2023

Greg_Taylor 

... or a more 'nuts and bolts' approach:

= LET(
    blockHeight,  6,
    blockWidth,  15,
    blockCount,   4,
    keyRow,       3,
    keyColumn,   14,
    keyRows,     SEQUENCE(blockCount, 1, keyRow, blockHeight),
    keyValue,    INDEX(data, keyRows, keyColumn),
    order,       SORTBY(SEQUENCE(blockCount), keyValue),
    rowNumber,   TOCOL(blockHeight*(order-1) + SEQUENCE(1,blockHeight)),
    CHOOSEROWS(data, rowNumber)
  )
  • Greg_Taylor's avatar
    Greg_Taylor
    Copper Contributor
    Dec 08, 2023
    Thank you SOOO much Mr. Bartholomew. There are about 6000 records.
    This is very encouraging; however, I am really rudimentary with Excel, so I don't know where to put this string 🙃.... Do I just put this in the first cell of the file?
    I'll experiment in the meantime. Is it possible to give me more "Idiot's Guide" directions? Thank you again

Resources