Grouping and sorting details

[Rich Stone]

First time in the Excel Section... Be gentle please!

I have a spreadsheet which lists all visits by all clients. With this
information, I wish to create a report that lists the most frequent visitors.
I think that the best way to do this is to group the client visits so I can
see how many visits each client has made and then sort the clients by this
number of visits. I really don't know where to start on this!

Any help please?

 

[ozgrid.com]

Hello,

Probably a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Rich Stone]

Thanks for your reply. Could you possibly explain how I would do this? I'm
afraid my excel skills don't stretch too far in this aspect and I've never
used pivot tables.

 

[Glenn]

Thanks for your reply. Could you possibly explain how I would do this? I'm
afraid my excel skills don't stretch too far in this aspect and I've never
used pivot tables.

Besides the link already provided by Dave, you can also look here for
introductions to PivotTables:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Check them out and see if you can work it out. They are not that difficult.
Otherwise, without a lot more information about your worksheet layout, it would
be difficult to help further.

 

[Rich Stone]

Thanks for the link, but I'm still in the dark on how this will help me. As
you suggested, here's some more info...

The spreadsheet is basically a table with the headings of:
- Visit date
- Client reference
- Client name
There are over 1000 clients included in the table with up to 20 visits per
client in some cases.

There are two steps I need to take. Firstly, count how many visits are
recorded per client. Secondly, list the top 10% of the most frequent
visitors. It sounds so simple but I am really struggling!

 

[Glenn]

Thanks for the link, but I'm still in the dark on how this will help me. As
you suggested, here's some more info...

The spreadsheet is basically a table with the headings of:
- Visit date
- Client reference
- Client name
There are over 1000 clients included in the table with up to 20 visits per
client in some cases.

There are two steps I need to take. Firstly, count how many visits are
recorded per client. Secondly, list the top 10% of the most frequent
visitors. It sounds so simple but I am really struggling!

Those links provide step by step instruction on how to create a PivotTable. Are
you able to get that far?


Again, more specifics about your worksheet layout would be necessary to provide
an exact solution. That would include the EXACT layout of your table, including
column and row references. Like this:

Headers are in row 1. Column A through C are as follows:

Visit date
Client reference
Client name

Data is in rows 2 through 15000.


Assuming that is the case, select cell A1 and then Data / PivotTable and
PivotChart Report. Click "Next". Confirm that the selected range includes your
headers and all data. Click "Next". Select "New Worksheet". Click "Layout".
Drag "Client name" to the Row area. Drag "Visit date" to the Data area. Click
"OK" and "Finish".

On the PivotTable that was created, right-click "Client name" and select "Field
Settings". Click "Advanced". Under AutoSort options, click "Descending" and
for Using field select "Count of Visit date". Under Top 10 AutoShow, click "On"
and confirm Top 10 and Using field "Count of Visit date". Click "OK" and "OK".

 

[Rich Stone]

Hi Glenn,

Thank you very much for your help. This has worked well in providing the
information I required. It has also given me a nice introduction into using
pivot tables for future projects!

I have just one further question... If I have further columns of data, such
as client contact number or address, for each row, is there a way of
displaying them on the pivot table without them having an effect on it's
initial function?

Richard

 

[Glenn]

Hi Glenn,

Thank you very much for your help. This has worked well in providing the
information I required. It has also given me a nice introduction into using
pivot tables for future projects!

I have just one further question... If I have further columns of data, such
as client contact number or address, for each row, is there a way of
displaying them on the pivot table without them having an effect on it's
initial function?

Richard

You can add more columns of data to the row area on the PivotTable, although you
may need to disable some automatic subtotals. Right click on the column name in
the PivotTable, select "Field Settings" and then "None" under Subtotals.