Forum Discussion
Sort to another table
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.
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.
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:
- 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.
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!
