UoL Library Blog

Develop, debate, innovate.

A little bit of an ILL Excel challenge

Posted by gazjjohnson on 23 August, 2010

I know there must be an elegant and clever solution to a problem I have.  I’ve acquired an Excel output with a long list of values – 8,000+ to be exact.  Each one is an instance when someone placed an interlibrary loan request last year for a book or a journal, and contains the title of a book or a journal.  Like so:

BOOKILL Sharing the Earth: the Rhetoric of Sustainable Development AC-STAFF
BOOKILL Shelley and Vitality RES-PG
BOOKILL Shelley’s satire: violence, exhortation, and authority RES-PG
BOOKILL Shipwreck Anthropology DL-TCPG
BOOKILL Shock, Memory and the Unconscious in Victorian Fiction TC-PG
BOOKILL Short Story Theory at a Crossroads AC-STAFF

 What I want to know:

  • Is there a way to group the list by most popular titles (e.g. those that appear more than once)?

You see in this way I can see books that are regularly requested for loan from the British Library , and likewise journals, that we could perhaps consider for purchasing.  Something I’m sure the information librarians would find useful.

Using COUNTIF= has been suggested, but to use this I’d need to know what was the most popular variable already.  I fear currently this goes beyond my Excel skills to solve, but I can’t be the first person to try and find this sort of information out.  Any and all suggestions are welcomed – otherwise I’m going to have to eyeball this list.

9 Responses to “A little bit of an ILL Excel challenge”

  1. Andy Ekins said

    countif seems reasonable….or am I missing something ???

    If the title is in column B then in a nother column next to it (column D perhaps) put this function for the first title (assuming the list starts on line 1):

    =COUNTIF(B:B,B1)

    then drag that function down so the next line has this function (and so on):

    =COUNTIF(B:B,B2)

    Then just sort the data by column D largest to smallest

    • Hmn, that looked like if was going to work (Book titles are in col D starting at D2 below the col tile) – but all the cell is showing is

      =countif(D:D,D2)

      – no value, just the formula?!?

      Because if that worked – your idea of sorting by D would be perfect (I think)

  2. ej217 said

    I’m guessing that the titles given are not consistent enough to use some kind of filter?

    • Yes, that is the big issue – in on set 2529 book titles – most of which are different. If I was looking for how many times a specific title appeared – easy peasy lemon squeezy – but I want to be able to show my subject librarians only the most heavily demanded items (e.g. those with 2 or more occurances).

  3. Excel has some built in functionality that I think will give you what you need – Pivot tables. This allows you to group – and count – sets of values. I just googled, and this looks like an OK intro http://www.cpearson.com/excel/pivots.htm – but it uses the ‘sum’ option in the ‘data’ bit of the table, whereas you’ll want to use the ‘count’ option.

    Hope that helps!

    • Cheers Owen, pivot tables are one of those things I’ve heard of but never got my head round what they can do for me (too long out of the lab these days!). I’ll have a look and see if they do the job (if I can’t get Andy’s solution to work!)

  4. Joe Ellison said

    As long as individual instances a single title aren’t too different, a pivot table is definitely the way to go. They’re the Excel equivalent of cross-tabulation in Access & other databases.

    This one is easy. Select the data range, click insert pivot table, tell Excel to put the table in a new worksheet. Use book title for the row label. If you want, you can use patron group as the column label, and then count of the BookILL column as the value. This will allow you to determine which books are most frequently requested by each patron class. If you want to use the table elsewhere, copy the entire thing and use paste special as values.

    I’d be happy to send a sample if you wish.

    • Hi Joe – thanks for this. So far it looks like the COUNTIF solution is doing what i needed. That said I’ll be giving the pivot table solution a go as there’s a whole lot more stats work I’ll be needing to get into in the coming weeks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: