Use Visual Basic for Excel to compare two large spreadsheets
Budget: $30 – $200 USD
Deadline: 1 week
Song Comparison Task
Overview: our company sells sheet music online. We need to find out which songs in our catalog also appear in the catalog of a publisher. So you will be working from two spreadsheets that we give you:
(a) Publisher Spreadsheet - a spreadsheet that lists all of the publisher’s songs
(b) Catalog Spreadsheet - a spreadsheet that lists all songs in our catalog
Your task is to give us a reusable spreadsheet that will run on a PC on Windows 10. This spreadsheet will be used to compare the two input spreadsheets and give us the matches. It will have two sheets: a “Main” sheet for user prompts, status updates, etc., and a “Results” sheet for holding results.
The “Main” sheet will display a “CLEAR” button, a text entry field labeled “Publisher Spreadsheet”, a text entry field labeled “Catalog Spreadsheet”, a “START” button, and a “row counter” and “match counter” described below.
When opened to the “Main” sheet, this spreadsheet should do the following things:
1. Allow user to click “CLEAR”. This resets the “row counter“ to zero, the “match counter“ to zero, and clears all contents from the Results sheet.
2. Allow user to enter a filename for the Publisher Spreadsheet
3. Allow user to enter a filename for the Catalog Spreadsheet
4. Allow user to click “START”. This will find and open the two spreadsheets specified by the user, and give an error message if unable to find and open either spreadsheet.
5. For each row in the Catalog Spreadsheet,
a. Add 1 to a “row counter” on the “Main” sheet
b. Ignore the row if Status “Delivered”.
c. Ignore the row if Contractor = “No Contractor”
d. Step through the Publisher Spreadsheet. For each row in the Publisher Spreadsheet,
i. compare the Title field in the current Publisher Spreadsheet row with the Tune Title field in the current row of the Catalog Spreadsheet. In both fields, remove leading and trailing spaces, ignore capitalization, remove any leading “A “, “An “ or “The “ from both fields, and remove any text in parentheses. So for example “A Goofy Christmas (Violin)” should be treated as “Goofy Christmas”.
ii. If the fields match, add 1 to a “match counter” on the “Main” sheet, and add a row to the Results sheet with all the following values:
• # from Catalog Spreadsheet
• Tune Title from Catalog Spreadsheet
• Author(s) from Catalog Spreadsheet
• Code from Publisher Spreadsheet
• ISRC from Publisher Spreadsheet
• ISWC from Publisher Spreadsheet
• Composer from Publisher Spreadsheet
• Title from Publisher Spreadsheet
• RMM Share from Publisher Spreadsheet
• Song Territory from Publisher Spreadsheet
6. When finished, close the two other spreadsheets and show a “Finished” message on the “Main” sheet.
There are two sample spreadsheets for your review, one is a sample Catalog Spreadsheet and one is a sample Publisher Spreadsheet. Each spreadsheet has one row highlighted in yellow, showing a match. And within those two yellow rows, the song titles are highlighted in orange, also showing a match.
Song Comparison Task
Overview: our company sells sheet music online. We need to find out which songs in our catalog also appear in the catalog of a publisher. So you will be working from two spreadsheets that we give you:
(a) Publisher Spreadsheet - a spreadsheet that lists all of the publisher’s songs
(b) Catalog Spreadsheet - a spreadsheet that lists all songs in our catalog
Your task is to give us a reusable spreadsheet that will run on a PC on Windows 10. This spreadsheet will be used to compare the two input spreadsheets and give us the matches. It will have two sheets: a “Main” sheet for user prompts, status updates, etc., and a “Results” sheet for holding results.
The “Main” sheet will display a “CLEAR” button, a text entry field labeled “Publisher Spreadsheet”, a text entry field labeled “Catalog Spreadsheet”, a “START” button, and a “row counter” and “match counter” described below.
When opened to the “Main” sheet, this spreadsheet should do the following things:
1. Allow user to click “CLEAR”. This resets the “row counter“ to zero, the “match counter“ to zero, and clears all contents from the Results sheet.
2. Allow user to enter a filename for the Publisher Spreadsheet
3. Allow user to enter a filename for the Catalog Spreadsheet
4. Allow user to click “START”. This will find and open the two spreadsheets specified by the user, and give an error message if unable to find and open either spreadsheet.
5. For each row in the Catalog Spreadsheet,
a. Add 1 to a “row counter” on the “Main” sheet
b. Ignore the row if Status “Delivered”.
c. Ignore the row if Contractor = “No Contractor”
d. Step through the Publisher Spreadsheet. For each row in the Publisher Spreadsheet,
i. compare the Title field in the current Publisher Spreadsheet row with the Tune Title field in the current row of the Catalog Spreadsheet. In both fields, remove leading and trailing spaces, ignore capitalization, remove any leading “A “, “An “ or “The “ from both fields, and remove any text in parentheses. So for example “A Goofy Christmas (Violin)” should be treated as “Goofy Christmas”.
ii. If the fields match, add 1 to a “match counter” on the “Main” sheet, and add a row to the Results sheet with all the following values:
• # from Catalog Spreadsheet
• Tune Title from Catalog Spreadsheet
• Author(s) from Catalog Spreadsheet
• Code from Publisher Spreadsheet
• ISRC from Publisher Spreadsheet
• ISWC from Publisher Spreadsheet
• Composer from Publisher Spreadsheet
• Title from Publisher Spreadsheet
• RMM Share from Publisher Spreadsheet
• Song Territory from Publisher Spreadsheet
6. When finished, close the two other spreadsheets and show a “Finished” message on the “Main” sheet.
There are two sample spreadsheets for your review, one is a sample Catalog Spreadsheet and one is a sample Publisher Spreadsheet. Each spreadsheet has one row highlighted in yellow, showing a match. And within those two yellow rows, the song titles are highlighted in orange, also showing a match.