360 Core: Matching Provider Title List with Client Center Title List

How do we compare a list of titles from a provider with a list of database titles from the Client Center and Serials Solutions' 360 KB knowledgebase?

1. From within Excel, open the Client Center title list you downloaded (using these instructions). For rest of these instructions, we will refer to this as the Client Center Spreadsheet.
   
   
2. Download and open the Example Spreadsheet for Matching Titles. This spreadsheet has two sheets: Client Center List (which looks like the title list you downloaded from the Client Center); and Provider List, which looks like a list you would receive from a provider. For the rest of these instructions, we will refer to this as the Example Spreadsheet.
   
   
3. Create a third Excel spreadsheet and copy your provider's actual title list into this new spreadsheet, formatting it like the Provider List on the Example Spreadsheet. (In other words, Column A needs to have the titles, Column B needs to have the ISSNs or ISBNs, and Column C needs to have the types.) For the rest of these instructions, we will refer to this third spreadsheet as the Provider Spreadsheet. Save the spreadsheet with a useful name, such as gale_virtual_reference.xls.
   
   
4. In your Client Center Spreadsheet, insert a new column before Column C (the "Type" column) entitled "Match?" (without the quotation marks). In other words, this will become the new Column C, and the Type column will become Column D.
   
   
5. In the second row of the "Match?" column, add the VLOOKUP formula, either from Cell C2 of the Example Spreadsheet's Client Center List, or copied from here:
   
   
   =VLOOKUP(B2,'Provider Spreadsheet'!B:B,1,FALSE)
   In the formula, replace the words 'Provider Spreadsheet' with the filename of your Provider Spreadsheet (but keep the single quotation marks before and after the filename).
   
   Here is some information about the formula:
   • Always maintain this structure for the formula: =vlookup(lookup_value, table_array, column_index_number,range_lookup).
   • The lookup_value equates to the original value you want to match and it will always be a single cell.
   • The table_array equates to the range of values that you want to match and it will always be the values from one column of the same type of information (which means multiple values to match against that original single value).
   • The table_array structure should be this (without quotes): ",'Title for the spreadsheet that you created'!Column_Letter:Column_Letter,".
   • The lookup_value and the table_array should have the same type of information; for example, ISSN to ISSN.
   • Always input 1 for the column_index_number.
   • Always input FALSE for the range_lookup.
   • You can match title to title, ISSN/ISBN to ISSN/ISBN, URL to URL or another unique identifier that exists in the Client Center, but we recommend that you match by ISSN/ISBN.
     
     
6. Copy the cell that contains the VLOOKUP formula and paste this formula in all the cells below it (staying in the same "Match?" column) in which you want to do a title match.
   
   
7. You should now see values in each cell the "Match?" column:
   • The value #N/A means these titles do not exist in this database in the Client Center. Repeat steps 1-6 above for another database to attempt to match them.
   • If there are ISSN/ISBN numbers (or whatever information you try to match on), then these titles are a match; that is, they exist in this database in the Client Center. To subscribe to these titles, continue with the next steps.
     
     
8. Sort all the titles (rows) by the "Match?" column so that all the #N/A values are aggregated to the bottom of the column.
   
   
9. Delete all the rows (or titles) that have the #N/A values by highlighting all the #N/A rows (by clicking on the row number) right clicking and selecting delete.
   
   
10. Delete the "Match?" column by highlighting the column (by clicking on the column letter) right clicking and selecting delete.
    
    
11. Close or delete the Provider Spreadsheet you created, because you won't need it any more.
    
    
12. Change the status of all the titles by typing Subscribed under the Status column (and make any other necessary adjustments to the information about the titles).
    
    
13. Save the file.
    
    
14. Upload the file to the Client Center with the rest of the ODSE instructions.