Sort Excel within a data group

[SimonSellick]

I have an Access app that creates an Excel sheet with data groups and collapses the detail so that just the summary rows show by default.

The users have asked to be able to sort the rows within a group, but the facility seems not to be available in my version of Excel (97).

Does anyone know whether later versions offer the facility, or can anyone think of another way to do it?

For example:

Group 1   score 12
 - sg 1.1        4
 - sg 1.2        8
Group 2   score  9
 - sg 2.1        3
 - sg 2.2        6

The users might want to sort within the group by score descending to get the highest ones at the top of each group. And they are likely to come back at some point and ask for the group headers to be sorted too!

Ther are actually two grouping levels, not just the one shown in the example.

Any ideas welcomed.

 

[SkipVought]

Hi,

Trouble is, you have a REPORT and not a table. There are all kinds of horrible problems trying to treat a REPORT like a table.

You would be much better off, querying the source data, sorting it the way you want and THEN, making a report.

Take a look at Data>Get External Data>New Database Query -- MS Access Database*......

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[SimonSellick]

Skip,

Thanks for your reply.

I might be being a little dense, but I don't really understand your point.

The results come from an Access front-end running a query which could sort in any desired order. The resulting ordered recordset is then written row-by-row to an Excel sheet. As each row is written, the group breaks are detected and Excel data groups are defined to match them.

On completion, the Excel data groups are 'rolled up' (hide detail) so on opening the worksheet, the user sees only the top-level groups but can drill down into the detail to see the components.

It is the order of the detail rows within a group level that the users want to alter, and they would like to be able to do it within the worksheet rather than by re-running the Access query with a different order specified and generating a new worksheet.

My Excel (97) disables the sort facility when data groups are defined.

Regards,
Simon.

 

[firefytr]

AFAIK, you would need to turn off the Groups to sort.

-----------
Regards,
Zack Barresse

 

[combo]

A more flexible tool to work with data is the excel pivot table. The source data set is used to create pivot reports or charts (xl 2k+). The newer excel version the more reporing capabilities.
Pivot table allows quick rearranging report, sorting, grouping, formatting to predefined layout, without interfering with source data.
Source data can be exported to excel, be stored in access table(s) or other external data source. In the later case MS Query tool can be used to link tables or filter data, it is also a way to overcome 64K rows limit of worksheet size if the output table size is smaller (I was working efficiently with 300k+ access table).
To start pivot table report, select any sell in data table (in excel) and go Data>Pivot table report. The wizard will guide you through the rest of creation.

combo