Room block reconciliation

Every year, we have a spreadsheet in Google Docs to help us make sure that we're on target for getting enough rooms to meet our minimums and reconcile hotel-provided delegate lists with digital records provided by hotel reservation services.

Creating the Spreadsheet for a New Year

1. As the SCIENCE records Google account, copy last year's sheet and share with the same people
2. On the Prices sheet, update the 100% room-night target and the price schedule
3. On the Previous Year Comparison sheet, archive the old year:
   1. Move the chart out of your way so you can see columns A-G
   2. Select columns B-D, right-click the column header, and select "Insert 3 right"
   3. Select columns B-D and press Ctrl+C. Select cell E1 and press Ctrl-V, then click the paste popup and choose "values only". Do the same thing again but choose "format only". This gives you a copy of the previous year with static values instead of the formulas.
   4. Update the header in cell B1 to the current year
   5. Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates
   6. Move the chart back to the top left
   7. Double-click the chart and add G3:G203 to the data range list between D and J
4. On the Master Account sheet, delete all the data - leave the headers and formulas (gray cells)
5. On the Passkey sheet, delete all the data - leave the headers and formulas (gray cells)
6. On the Phone sheet, delete all the data - leave the headers and formulas (gray cells)
7. On the GSR sheet, delete all the data - leave the headers and formulas (gray cells)
8. On the BACON sheet, delete all the data - leave the headers
9. On the Reconcile sheet, delete the confirmation numbers in columns A and B

Importing from Passkey

1. Log into Passkey.
2. Open the event for the current year.
3. Click "Room List Report" under the room count.
4. Hover "Actions" in the top right and click "Export"
5. In the export dialog select "Comma Separated Value (.csv)" format and click "Export"
6. In the Google Sheet, navigate to the Passkey tab and select cell H1 (the header of the first non-gray column)
7. Click File -> Import and upload the CSV you downloaded from Passkey
8. In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"
9. Extend the formulas in A:G (the gray columns) as necessary
10. Ctrl+Click to select the "ResAckNo" for each row with "No" for "Reconcile" (highlighted yellow), then copy with Ctrl+C
11. Switch to the Reconcile tab and paste special as "values only" at the end of column B
12. Extend formula columns C:M as necessary.

Importing from BACON

1. In the database, run

   SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')

2. Export the result to your favorite spreadsheet format.
3. Copy the data without headers to A2 of the BACON worksheet (the first data row of the "Confirmation" column).

Importing from the delegate list

1. Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in 2019, whose email address is her name with a dot between the first and last names at GrandSierraResort.com).
2. Import and reconcile Passkey, so that's up to date.
3. Take the spreadsheet they give us and copy it into the GSR worksheet. It will not necessarily match the columns, so just try to make them match.
4. Extend formula columns A and B as necessary.
5. Open up the GSR worksheet and the Reconcile worksheet, side by side.
6. For each item in the GSR worksheet where the Reconcile column is 0, try to find matching Passkey confirmation code in the Reconcile worksheet, using the name and dates.
   • If you find it, put the GSR confirmation code in the GSR confirmation column (column A) next to the matching Passkey confirmation code.
   • If you don't, put the GSR confirmation number at the end.
7. Extend formula columns C to M as necessary.
8. For all the red rows that are marked missing, check if they match any of the master account rooms, add the GSR confirmation number to the Master Account Rooms table in the Confirmation column (column A).
9. For all the remaining missing rows, put those confirmation codes into the confirmation column of the Phone worksheet (column A).
10. Extend formula columns B to I as necessary.
11. Add the Phone confirmation codes to BACON, using the raw database.
12. Check VIP tickets in RT and handle appropriately.