Pivot Table order problem

[UKSteveH]

I have a large database (185,000 lines) of the UK pop charts, week by week. It has Date, Artist, Title, Chart Position, plus various other fields. The database is in a strict order by date (old-new) and then by Position (1-100). It is currently in an Access Database and I am using the external data functions in Excel

I am trying to create a pivot table that shows the first Date that an Artist entered the chart. Fairly straightforward. I also want the the results in Date order. Again easy to do. - Row Label - Artist Value - Min of Date. Then sorted a-z on the value field.

What I do not understand is the order Excel insists on putting them. It is not alphabetical and it is not in the same order as the database. But it is always in the same order, which for some reason is record numbers 2 then 8 then 6, 1, 4, 10, 7, 15, etc.

This order is the same if I link directly to the database and pull out the raw data or go directly from Access to a Pivot Table. It also uses the same order if I use just a small part of the data copied and pasted into a new spreadsheet (ie not associated to the external database).

I would prefer the final table to be in the same order as the database. Anyone got any ideas why this happens and any ideas on how to get around it?

Steve

[Portuga]

I am trying to create a pivot table that shows the first Date that an Artist entered the chart. Fairly straightforward. I also want the the results in Date order. Again easy to do. - Row Label - Artist Value - Min of Date. Then sorted a-z on the value field.
Steve

You can use the "sort option" in the pivot table features.

You can sort alphabetically in the row field by selecting the "artist" field and in field configuration: "ascending".

[UKSteveH]

Sorry that does not work for me!

The final sort order should be in the same order as the database. In this case Al Martino is the first record in the database and should be the first in the pivot table.

The problem lies in the fact that the Date is the same for several entries, I don't want alphabetical Artists. The order is actually by Date then Position, but Position is not in the pivot table (if it was I would get duplicate artists)

As I see it, there is no logical reason why the first 15 records of the pivot table are not in the same order as the database.

The only way I can see around this would be to add xxx seconds or minutes to the Date where xxx is the same value as the chart position. This would mean that the the sort would work. But this seems a rather long winded method to get the data back to the way it originally appeared!

I have attached a sample of the data (first 100 lines) and a pivot table is on Sheet4. Try and get the results in a different order.

Steve

[Portuga]

You cant sort by position, if position is not in the pivot table.

Try this and see if it suits you.

(sorted by min of position)

(attached)

[UKSteveH]

Thanks. I have already tried that.

If only I could understand how Excel determines the order that it displays them...

Obviously one of those Microsoft Moments! unless someone has a better idea...

[ptm0412]

I wonder if you are using Excel 2003 or 2007?
Your Pivot in sheet4 can not be sorted in 2003 but can be sorted in 2007. And I guess it is not an Excel Pivot, perhaps an Access Pivot.
Anyway, I have made another one in the same sheet with data, by Excel 2003. It seems autosort after be made. Look the attachment.