Sorting data in Excel

[Attleborough]

I have an excel sheet in which I have 4 columns lets say -

Date, Issue, Fix, Responsibility. Something like the attached View attachment Book1.xls

I'm making a book that will have one month per sheet.

What I'd like to be able to do is at the end of the month sort the data so that I can see how many of each issue I've had (Greatest at the top) and then use that summary (or the top three) to prepare a graph or pie chart.

I had a sheet years ago that I'm sure allowed me to do this but I've forgotten how I did it and I can't find it to copy.

Can any of the resident excel experts tell me if this is possible (I'm not imagining it am I?) and give me some pointers please on how to do it?

Thanks

 

[Blowzorn]

Hilight the table ensuring your column titles are the top row.
Then from the menu bar choose data, sort.
The dialog box will show your column headings so choose issue in the sort by box then date in the then by box and voila you should have something like this.


Date Issue Fix Responsibility
09/05/2008 Brake Pads Replace Owner
10/05/2008 Brake Pads Replace Owner
07/05/2008 Cracked Screen Replace Warrenty
04/05/2008 Cylinder Head Gasket Replace Owner
06/05/2008 Exhaust Replace Warrenty
08/05/2008 Exhaust Repair Owner
01/05/2008 Faulty Seat Repair Owner
03/05/2008 Faulty Seat Repair Owner
02/05/2008 Headlight bulb Replace Warrenty
05/05/2008 Split Tyre Replace Warrenty

 

[Clive]

Hilight the table ensuring your column titles are the top row.
Then from the menu bar choose data, sort.
The dialog box will show your column headings so choose issue in the sort by box then date in the then by box and voila you should have something like this.


Date Issue Fix Responsibility
09/05/2008 Brake Pads Replace Owner
10/05/2008 Brake Pads Replace Owner
07/05/2008 Cracked Screen Replace Warrenty
04/05/2008 Cylinder Head Gasket Replace Owner
06/05/2008 Exhaust Replace Warrenty
08/05/2008 Exhaust Repair Owner
01/05/2008 Faulty Seat Repair Owner
03/05/2008 Faulty Seat Repair Owner
02/05/2008 Headlight bulb Replace Warrenty
05/05/2008 Split Tyre Replace Warrenty

then you will need to Data/Subtotal - on each change of "Issue" - Count
to get

Date Issue Fix Responsibility
09/05/2008 Brake Pads Replace Owner
10/05/2008 Brake Pads Replace Owner
Brake Pads Count 2
07/05/2008 Cracked Screen Replace Warrenty
Cracked Screen Count 1
04/05/2008 Cylinder Head Gasket Replace Owner
Cylinder Head Gasket Count 1
06/05/2008 Exhaust Replace Warrenty
08/05/2008 Exhaust Repair Owner
Exhaust Count 2
01/05/2008 Faulty Seat Repair Owner
03/05/2008 Faulty Seat Repair Owner
Faulty Seat Count 2
02/05/2008 Headlight bulb Replace Warrenty
Headlight bulb Count 1
05/05/2008 Split Tyre Replace Warrenty
Split Tyre Count 1
Grand Count 10


then hide the individual items (collapse item 2) to be left with

Date Issue Fix Responsibility
Brake Pads Count 2
Cracked Screen Count 1
Cylinder Head Gasket Count 1
Exhaust Count 2
Faulty Seat Count 2
Headlight bulb Count 1
Split Tyre Count 1
Grand Count 10

then highlight the "Subtotalled details" and resort Data/Sort on the subtotal count
(Column E) to get

Date Issue Fix Responsibility
Brake Pads Count 2
Exhaust Count 2
Faulty Seat Count 2
Cracked Screen Count 1
Cylinder Head Gasket Count 1
Headlight bulb Count 1
Split Tyre Count 1
Grand Count 10

 

[Pathologic]

Try Auto filter

If you are using 2003 or above why not set up an auto filter of the whole table. Click on any field in your table header. Go to the Data menu, select Filter then Autofilter. If it works, you should see small dropdown arrows appear in each of the header cells. Click on one of these and you can filter the table on any of the values in the cells of that column.

I set up a formula to calculate the year month based on a transaction date for each record.I could the easily filter down to the month of interest. This saves having multiple worksheets unless you want them.

In the drop down menu you can scroll up to find sort ascending and descending options. Auto filter keeps records in the table together. The ordinary sort option could scamble records if you don't select every column in the table.

Finally, there is a custom filter option with standard logic such as les than and greater than.

Try it out, see what you think.

Regards,

path.

 

[Attleborough]

All,

Thank you for your suggestions - Clive, your method is closest to the result I am looking for, (though dammed if I can get the figures to show in a pie chart) clearly I need to spend a little less time writing (dull) reports and a bit more using excel!

Thanks

 

[Clive]

(though dammed if I can get the figures to show in a pie chart)

Go through the Chart wizard and select the "Totals" first then the issues values as "Categories"

 

[Attleborough]

Thanks Clive,

Three years ago I spent nearly all day every day in excel. now I hardly open it and it's been a sobering experiance to see just how much I've forgotten.

Thanks for your help.

 

[AlanB]

I recommend you investigate the Pivot table method in Excel. It'll do what you are wanting to do, but offers you multiple, multiple ways of looking at the same data by drag and drop techniques.

 

[Garryc]

....AlanB right,

...pivot tables is the way to and once you have it setup its easy to change the range, on the various worksheets within the file.

Goto the MSOffice home page for tips on pivot tables, lots of good info / advice....