Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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,

share|improve this question
    
Pivot Table or advanced filtering would do exactly this –  Raystafarian Feb 23 '12 at 16:47
    
OK, that's great news, but I have no idea how to do either... :) –  dunc Feb 23 '12 at 16:48
    
what version of excel are you using –  Raystafarian Feb 23 '12 at 16:48
    
I'm using Microsoft Excel 2010. I'll update my OP to clarify that! –  dunc Feb 23 '12 at 17:05
    
You want to know how many unique values in column G appear when column H reads Teachers OR Staff OR Administrator? –  Raystafarian Feb 23 '12 at 18:17

3 Answers 3

up vote 3 down vote accepted

To use a Pivot Table:

Import your data into an Excel workbook and add the column titles if they are missing.

picture of data in Excel with column titles

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 click OK.

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 the PivotTable Tools Options tab.)

PivotTable field list

Drag fields to the appropriate part of the pivot table. i.e.

  • Profile field to Report Filter
  • User Login to Row Lables
  • Date to Values (After you move it, if it doesn't says Count of Date, then click the drop-down arrow, select Value Field Settings and change Summarize Values By to Count.)

That will give you a pivot table that looks like this.

finished PivotTable

To see a count for each Page Accessed, drag that field to Column Labels.

altered PivotTable

Feel free to explore and play with the PivotTable. Move things around, add more fields to Row Labels or Column Labels etc. If you can't figure out how to do a specific thing, ask another question. :-)

share|improve this answer
    
Incredible post, many thanks! :) I'll look at this when I get to work tomorrow and let you know how I get on. –  dunc Feb 23 '12 at 22:14
    
Worked perfectly. Many thanks! –  dunc Feb 24 '12 at 11:46

Without really seeing how it's set up and showing you how to do a pivot table, this formula will count for you.

=SUM(COUNTIFS(H:H,"cgi",G:G, {"administrator","staff","teachers"}))


You could also select the data go to insert - pivot table and put column H as row labels and column g as column labels and put column g in the sum of values field, but change it to count instead of sum by clicking and selecting value field settings

share|improve this answer

say Profile is in column H code formula in column I2 "=IF(OR(H2="tEACHERS",H2=sTAFF",h2="Admin")),1,0) - this flags the entries you want with 1, 0 otherwise. sum column I for total

share|improve this answer
    
This won't give me a running total of the number of hits each member of staff has made will it? –  dunc Feb 23 '12 at 18:04
    
We need to count column G as well –  Raystafarian Feb 23 '12 at 18:12
    
How many users are you keeping track of? @Raystafarian is right that a pivot table would work for this. Or do a simple sumif formula on the users found in G in an adjacent column –  datatoo Feb 23 '12 at 18:19
    
There are around 80 members of staff. –  dunc Feb 23 '12 at 18:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.