Prev: Receive data in column with pull down
Next: i enter value in one column the next column existing value turn 0
From: Glenn on 12 May 2010 11:31
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".
From: Rich Stone on 13 May 2010 05:13
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
From: Glenn on 13 May 2010 10:11
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.
First  |  Prev  | 
Pages: 1 2
Prev: Receive data in column with pull down
Next: i enter value in one column the next column existing value turn 0