Pivot Table custom sort

[crazydragon84]

Hi,

I have set of transaction data that has various fields such as name, date, amount (and various other attributes of the transactions). I have 4 pivot tables that splits this data into different sets of data (mainly divided by transaction types like buys, sells, fees, etc.). the pivots have certain filters and conditions to show only specific type of data. The pivot tables have no Values, just rows (this is because I want to see by transaction, not by aggregated amounts). For instance, if ABC had $200 and $300 transaction on 3/1 and 3/15, I want to see this twice. I copy this pivot table data to a "Final report" that gets used elsewhere in the firm.

The problem is, I want to keep the name field as the first field, but sort it by date column, which is in second (or later) field. When I try to "Sort oldest to newest" it does, but only after it sorts by the first column (Name). Thus, ABC's 3/1 and 3/15 transactions will always show up before XYZ's 3/2 transaction. Is there a way to do this automatically (without using vba), or do I just need to copy and paste the data then sort it afterwards?

 

[Fazza]

Automatically, without using VBA - suggest you use a query (table) instead (of a pivot table). Can be set to refresh every n minutes.

 

[crazydragon84]

interesting. i never knew this feature, but it looks like it requires the underlying data to be in a table format. so my raw data is extracted to a data tab using an excel addin (sql base). the addin will delete all the data in the tab, then refreshes the tab with new sets up data (which means the underlying table will disappear everytime I refresh the table). Any workarounds? I do use a named range on the data.

 

[Fazza]

not sure I understand.

yes, the underlying data needs to be in a table format. (so, same as is done currently for the pivot table?)

the addin will delete all data in the tab, then refreshes the tab with new sets up data
Well, with new data that is what you want. And it has a named range. This suits a query very well. Just refresh the query. Not sure why a workaround is mentioned.

the query is simple enough in practice. it is defined/set-up, and then refreshed as required.

I need more explanation/understanding of the situation to be able to advise