Sort to another table

[djpaul]

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'd like to sort all the "A"s to another table (table2)

I'd like them listed in number(column 2) order, high to low with their corresponding name (column 3)

Example:

124  Ann

120  Betty

65   Bob

My goal is to sort table1 to:

table 2 listing all the "A's

table 3 listing all the "B's

table 4 listing all the "C"s

I can sort table 1 to table 2, but I can't break out just the "A"s.

Any help would be greatly appreciated.

[#2]

@djpaul Perhaps I'm simplifying your problem, but why not Custom Sort Table1. First by ABC (A-Z), Then by Number (Largest to Smallest). Put filter buttons on the table. Filter out all A's, copy and paste to a new table. Repeat for B's and C's. This would be fairly straight-forward, though I admit it's not

dynamic or automated. 

[#3]

@Riny_van_Eekelen 

The data in table 1 changes on a regular basis. I'd like to automate the process.

When the data in table 1 changes, tables 2,3 and 4 would automatically update with no intervention.

[#4]

@djpaul OK. Then I would get into PowerQuery. Once set-up properly, you just need to do a refresh if Table1 changes, and that could even be automated.

See attached.

If you are not familiar with PowerQuery, this could be a good starting point:

https://exceloffthegrid.com/power-query-introduction/ 

[#5]

Thanks for the information. I was hoping someone out there had a relatively easy way of doing this using something like a VLOOKUP or SORTIF command.

[#6]

@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!

[#7]

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