Forum Discussion
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...
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!
- Thank you Peter,
I have a laptop with Office 365. It looks like I should use it for this project.
David
