Searching a complex set of data

Question

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?

peterbartholomew1 · Answer

Greg_Taylor&nbsp;&nbsp;= LET(
    blockHeight,    6,
    blockWidth,    15,
    keyRow,         3,
    keyColumn,     14,
    singleRows,    WRAPROWS(TOCOL(data), blockHeight*blockWidth),
    sortedRecords, SORT(singleRows, (keyRow-1)*blockWidth+keyColumn),
    displayed,     WRAPROWS(TOCOL(sortedRecords), blockWidth),
    displayed
  )&nbsp;Another strategy might be required if you have more than 10,000 records

peterbartholomew1 · Answer

Greg_Taylor&nbsp;... 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 · Answer

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

peterbartholomew1 · Answer

Greg_Taylor&nbsp;The key feature of the formulas is that they are written for Excel 365.&nbsp; 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.&nbsp;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.&nbsp;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.&nbsp;If you cannot access 365, similar results may be available to you using PowerQuery.

lorenzo · Answer

Hi&nbsp;Greg_Taylor&nbsp;&nbsp;Attached is a Get &amp; Transform aka Power Query option:&nbsp;// Table
let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    TableSplit = Table.Split(Source, 6),
    ToTable = Table.FromList(TableSplit,
        Splitter.SplitByNothing(), {"RECORD"}, null, ExtraValues.Error
    ),
    SortBy = Table.AddColumn(ToTable, "SortBy", each
        Table.First( Table.Skip([RECORD], 2) )[N]
    ),
    SortedRows = Table.Sort(SortBy, {{"SortBy", Order.Ascending}}),
    RemovedSortBy = Table.RemoveColumns(SortedRows, {"SortBy"}),
    CombinedRecords = Table.Combine(RemovedSortBy[RECORD])
in
    CombinedRecords&nbsp;&nbsp;- Put your data in&nbsp;Table sheet 'Input'- Switch to sheet 'Output'- Right-click in a cell below A-O &gt; Refresh

Forum Discussion

Searching a complex set of data

Resources