Thank you to all of you who offered your thoughts on my query, I am very grateful.

 

 

 

Another option in Excel is Conditional Formatting.  Paste both entitlement lists into one spreadsheet. I usually “bold” one title list to differentiate it from the other. You may have to move columns around in one entitlement list to get all the ISSNs lined up in one column. (Be careful that you don’t scramble your data while doing this). Select the ISSN column and apply Conditional Formatting on duplicates. This defaults to shading the duplicate cells pink. Then select each entitlement list (not the entire spreadsheet) and sort by cell color (beware it can take some time). This will show you the duplicates in pink, and unique titles with no shading.

 

Alternatively you can use MS Access to match ISSN from two data sources. You have to import the title lists as two “tables” and allow the column headings to become the column names in Access. Then run a Query on the two tables that matches on ISSN, and you’ll get a list of the duplicates. I can’t really explain this in a plain text email, I suggest looking up videos if you don’t know how to do this (thank you to DU’s Chris Brown for a workshop on Access).

 

Both methods rely on the ISSN being present, and that it is clean and correct. Obviously there will be at least a few errors when working with large entitlement lists.

 

You can do some messy matching on titles but that requires a lot more cleanup. I usually do ISSNs first with Conditional Formatting for duplicates, then repeat with titles. Since publishers often don’t treat titles the same, I insert another column called “Clean title”, copy and paste the journal titles into the Clean title column and do a lot of Find & Replace operations such as removing initial articles and turning ampersands (&) into “ands”, then Conditional Format for duplicates. You can iterate this another time by lopping off subtitles demarcated with colons or semicolons, and get rid of any trailing spaces or double spaces.  Some of this you can do in Python IDLE by copying columns into doc strings (triple quotes) and performing simple string-slicing operations, then print out the output and paste the output (carefully) back into Excel.

 

Eventually you get into a long tail of acronyms, diacritics and other oddities, and it’s up to you how much you want to hand-check or cleanup those journal titles.

 

Diane Westerfield, Electronic Resources & Serials Librarian

Tutt Library, Colorado College

diane.westerfield@coloradocollege.edu

(719) 389-6661

 

 

 

From: Serials in Libraries Discussion Forum [mailto:SERIALST@LISTSERV.NASIG.ORG] On Behalf Of Melissa Belvadi
Sent: Tuesday, December 12, 2017 12:05 PM
To: SERIALST@LISTSERV.NASIG.ORG
Subject: Re: [SERIALST] Journal collections; help determining overlap/duplication

 

 

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