I have a CSV file containing 100,000 rows.
Each row consists of information under a number of columns, such as "Date", "Page Accessed", "Page Type", "IP", "User Surname", "User Forename", "User Login", "Profile".
A few example rows may therefore look like:
A B C D E F G H
18:48:42 Home Drive web page 90.90.90.90 Gill C cgi Teachers
20:48:42 Dashboard web page 90.90.90.90 James R rj Teachers
22:48:42 Shared Drive web page 90.90.90.90 Gill C cgi Teachers
These rows are an individual hit on our Virtual Learning Environment.
I would like to determine how many times each user has made a hit on our VLE.
As such, I would imagine that I need to get excel to count the number of times each User Login
appears in the list of rows where Teachers
, Staff
or Administrator
is the value for "Profile".
What's the easiest way of going about this?
I'm using Microsoft Excel 2010.
Thanks in advance,
Best Answer
To use a Pivot Table:
Import your data into an Excel workbook and add the column titles if they are missing.
Select a cell in the data and then click
Insert
->PivotTable
. A Create PivotTable dialog box will display. Make sure it correctly selected your entire range and clickOK
.It will add a new sheet to your workbook with the PivotTable Field List displayed on the right. (If it is missing, click the Show
Field List
button on thePivotTable Tools
Options
tab.)Drag fields to the appropriate part of the pivot table. i.e.
Report Filter
Row Lables
Values
(After you move it, if it doesn't saysCount of Date
, then click the drop-down arrow, selectValue Field Settings
and changeSummarize Values By
toCount
.)That will give you a pivot table that looks like this.
To see a count for each Page Accessed, drag that field to
Column Labels
.Feel free to explore and play with the PivotTable. Move things around, add more fields to
Row Labels
orColumn Labels
etc. If you can't figure out how to do a specific thing, ask another question. :-)