Step 1.) Sort titles with a conditional format option to highlight duplicate ISSNs and titles, try to ensure duplicate titles have matching ISSNs
Step 2.) Remove titles without ISSNs
Step 3.) Sort by ISSN with a secondary sort by platform
Step 4.) Run formula that shows if 1st row issn matches 2nd row issn then it will take the platform or the count
First column header (after column headers for title, publisher, ISSN) - Platform 1; Second column header - total downloads for P1; Third column header - Platform 2; Fourth column header - total downloads for P2 etc
FORMULA for Platform and downloads to shift data from column to appropriate row:
| =IF(C2=C3,D3,"") | =IF(C2=C3,E3,"") | =IF(C2=C4,D4,"") | =IF(C2=C4,E4,"") | =IF(C2=C5,D5,"") |
STEP 5.) Insert a first column with a formula that says if the 2nd row issn matches the 1st row issn then delete
FORMULA:
| =IF(C2=C1,"DELETE","KEEP") |
Step 1.) Ensure list of requested titles includes ISSNs
Step 2.) Sort Excel list by journal title, fill in any missing ISSNs and ensure ISSNs with duplicative titles match, it may be useful for use the conditional formatting for the title and ISSN column
Step 3.) Sort the Excel sheet by ISSN, If there are ISSNs missing dashes (-) these may be easy to spot or use the filter for does not contain - . Once these cells are found, try the following strategies:
1st strategy for missing 0s- highlight those with a missing dash, use the custom number option, type 0000-0000; for those reformatted cells use the formula =TEXT(cellnum, "0000-0000")
2nd strategy for 8 digits included- Use the formula =REPLACE(cellnum,5,0,"-")
3rd strategy for 8 digits included, more complicated- Use the formula =Left(cellnum,4) & "-" & RIGHT(cellum,4)
Step 4.) Copy the list with the cleaned up ISSNs into a new tab within the spreadsheet, remove duplicates based on the ISSN column only, delete the column with any ILL request data, label this tab as the Master List
Step 5.) Within the Master List tab create a new column that represents total ILL requests. To do this use SUMIF formula. So if the ISSN column is in the 'A column' within the first tab and the second tab, and the ILL requests are in the C column of the first tab enter the following formula =SUMIF(A:A of the first tab,A2 of the masterlist tab,C:C of the first tab)
To add a dash to an ISSN:
=REPLACE(cellnum,5,0,"-") OR if it's missing 0s =TEXT(cellnum,"0000-0000")
To remove a dash to an ISSN:
=SUBSTITUTE(cellnum,"-",4)
Step 1.) Find a common data field between the two tabs/spreadsheets (ie ISSNs; MMS IDs; title etc)
Step 2.) Copy and paste this common field so this common field is listed in the first column of both tabs/spreadsheets then sort this column
Step 3.) Now use the Vlookup formula. Go to the spreadsheet where you want to include additional data and create an appropriately labeled column header. Now enter =Vlookup(cellnum of column A of tab where data will be added, highlighted data selection from other tab/spreadsheet where data is located, count the number of columns on the other spreadsheet where data is located ie if the data is in the 4th column enter 4, and the word FALSE)
A formula may look something like this =VLOOKUP(A2,'FY20'!A2:E108,2,FALSE)
Step 1.) Setup a tab with breakdown of use listing columns with journal title; ISSN; Cost; Usage; Cost per Use; % of Total Use (title use/overall use*100)
Step 2.) Add a column with Cumulative Total % of Use with Low Cost per Use
This is sorted by smallest cost/use then first cell is requests/request sum and following cells are Cum total % above +(requests/requests sum)
Step 3.) Add a column with Total Titles of Use with Low Cost per use
This is a series based on cumulative % total
Step 4.) Add a column with Portion of Cost per Use
This is the cost per use/sum of cost per use
Step 5.) Add a column with Cumulative Total % with high use
This is sorted by highest use then divided request by total request and then the previous number added to the use/total use
Step 6.) Add a column with that counts the total number of titles as sorted by high use
Step 7.) Add a column with Portion of Requests
This is the usage divided by the sum of usage
Step 8.) Add a Request per Title column
This is the usage divided by the total number of titles
Step 9.) Create a table with Most Popular Titles- Targeting Titles with Highest Requests
This includes Portion of Use % column (30%, 40%, 50% and onward up to 100%)
Then include # of titles; cost; & min use per titles within each Portion of Use % category
Step 10.) Create a table with the Best Deal Titles- Targeting titles with the lowest cost per use
This includes Portion of Use % column (30%, 40%, 50% and onward up to 100%)
Then include # of titles; cost; & max cost per use within each Portion of Use % category
Step 11.) Create a final table with Cost per use Ranges
This table includes Cost per Use range (ie Under $1 CPU; Under $2 CPU; etc)
Then include cost; # of titles; portion of cost; portion of titles