A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Grouping and sorting details



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 11:37 AM posted to microsoft.public.excel.worksheet.functions
Rich Stone
external usenet poster
 
Posts: 52
Default Grouping and sorting details

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?
  #2  
Old May 12th, 2010, 11:41 AM posted to microsoft.public.excel.worksheet.functions
ozgrid.com
external usenet poster
 
Posts: 328
Default Grouping and sorting details

Hello,

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



--
Regards
Dave Hawley
www.ozgrid.com
"Rich Stone" wrote in message
...
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?


  #3  
Old May 12th, 2010, 02:21 PM posted to microsoft.public.excel.worksheet.functions
Rich Stone
external usenet poster
 
Posts: 52
Default Grouping and sorting details

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.
  #4  
Old May 12th, 2010, 02:55 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default Grouping and sorting details

Rich Stone wrote:
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.
  #5  
Old May 12th, 2010, 03:58 PM posted to microsoft.public.excel.worksheet.functions
Rich Stone
external usenet poster
 
Posts: 52
Default Grouping and sorting details

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!
  #6  
Old May 12th, 2010, 04:31 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default Grouping and sorting details

Rich Stone wrote:
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".
  #7  
Old May 13th, 2010, 10:13 AM posted to microsoft.public.excel.worksheet.functions
Rich Stone
external usenet poster
 
Posts: 52
Default Grouping and sorting details

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
  #8  
Old May 13th, 2010, 03:11 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default Grouping and sorting details

Rich Stone wrote:
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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.