Forum Discussion

Copper Contributor

Aug 12, 2021

Sort to another table

Windows 8.1 Excel 2016 I have a table (table1) with 3 columns. Cells in column 1 contain a single letter (A,B,or C) Column 2 contains a whole number (0-999) Column 3 contains a name (ex: Ann) I...

excel

PeterBartholomew1

Silver Contributor

Aug 13, 2021

djpaul

The trouble is that the formulas tend to be a mess without PQ or dynamic arrays. Given an extra column in your tables showing the record numbers, you could have

= LOOKUP(
    SMALL(
      IF(
        Table1[Letter]="A", 
        Table1[RecordNum]), 
      [@RecordNnum]), 
    Table1[RecordNum], 
    Table1[Name]
  )

Compare this with Excel 365

= FILTER(Table1,Table1[Letter]="A")

and you should see why I am thankful to see the back of old-style spreadsheets!

djpaul

Copper Contributor

Aug 13, 2021

Thank you Peter,
I have a laptop with Office 365. It looks like I should use it for this project.
David