Hi Rebecca,

I had all the problems you had with entitlement lists. I'm working on cleaning up our knowledge base and straightening out our ejournal holdings using the entitlement lists from vendors. I've been trying to figure out ways to automate the process, but so far I feel it's only possible to break up the entitlement list from a specific vendor into several parts and automate each part separately using Excel functions. 

The Excel functions I use most for this purpose are the basic ones: LEFT, RIGHT, IF THEN, MATCH, and conditional formatting. The LEFT and RIGHT functions are for reformatting the start dates to make them consistent with the date format in KB. Recently our KB (Alma) implemented an enhancement where you can include month and date in the dates, which will probably alleviate or eliminate the need to reformat the dates. The IF THEN is used mainly to find dates that fall into certain groups and so that I can modify the dates in a batch. For example, if our access start date is earlier than the journal start date, which happens when the vendor applies the same dates for all the  historical titles for the same journal, I'll just change our access start date to the journal start date in this case. 

I'd be happy to know if people have better and automated ways to do this. 

Thanks

Chenwei Zhao
Electronic Resources Librarian
University of Connecticut Library

2016-11-28 14:43 GMT-05:00 Blackman, Christine <cblackma@williams.edu>:
Hi Rebecca,

I often use and Excel forumula to compare unique identifiers in urls between a publisher list and a KB list for a particular database.  I've never tried to compare coverage dates this way however because of the discrepancies in the way the data is entered.

The formula compares all data in one column with all data in another column on another sheet (or not) and gives a TRUE/FALSE result for matches.  It must be entered as an array formula.

The basic formula is 
=OR(EXACT(A2, Sheet2!A$2:A$10))
after entering in a cell, hit F2 and Ctrl Shift Enter resulting in an array formula
{=OR(EXACT(A2, Sheet2!A$2:A$10))}

This may help to solve some of your problem as I never found that I could get VLOOKUP to work properly if I was trying to match many datapoints at once.

Best,
Chris


Christine W. Blackman
Catalog Librarian
Williams College Libraries

On Mon, Nov 28, 2016 at 1:53 PM, Rebecca Kemp Goldfinger <rkemp@umd.edu> wrote:
(Cross-posted)

Dear Colleagues,

Do you have a good method or set of procedures worked out for comparing e-journal entitlements lists against what is activated and/or able to be activated in your knowledge base (KB)?  I'm defining "entitlements lists" as lists of the e-journal titles and the subscribed/purchased coverage to which we are entitled.  These lists can be e-mailed by publisher representatives or downloaded from publisher websites.  Our knowledge base is WorldCat KB, and the KB files we compare against are KBART files, but I am interested in looking at procedures that anyone may be using, regardless of which KB you have.

We have tried using Excel's VLOOKUP functionality to match titles by unique identifier, but it seems as though there are lots of problems ​in the vendor supplied data ​that make it hard to compare coverage (and even titles, sometimes). A few problems we've run up against are entitlements lists that do not show all title changes with separate titles, ISSNs, and coverage for all the titles in the history, and entitlements lists that show separate lines for each subscribed year of the title, which makes it hard to pull together the true start date and end date into one KB entry.

If you have a process that you think works pretty well for matching up entitlements with what's available in the KB, would you mind contacting me?  I would love to learn about your process(es) and the tools you use. We are especially interested in automating the process with Excel or Google Sheet templates, if anyone has been doing that.​ Or are there any workshops or presentations that anyone knows of on this topic?

Has anyone had any success in getting better entitlements data from publishers?

Let me know if I need to clarify my request. Also, if there is interest in my gathering together responses and sharing to the lists, I would be happy to do that.

Many thanks for considering,
Rebecca

Rebecca Kemp Goldfinger
Continuing Resources Librarian
University of Maryland, College Park
Acquisitions Department, Room 2200
McKeldin Library, 7649 Library Lane
College Park, MD 20742
301-405-9309  (phone)



To unsubscribe from the SERIALST list, click the following link:
http://listserv.nasig.org/scripts/wa-NASIG.exe?SUBED1=SERIALST&A=1



To unsubscribe from the SERIALST list, click the following link:
http://listserv.nasig.org/scripts/wa-NASIG.exe?SUBED1=SERIALST&A=1



To unsubscribe from the SERIALST list, click the following link:
http://listserv.nasig.org/scripts/wa-NASIG.exe?SUBED1=SERIALST&A=1