This tutorial will guide you in matching the guest's nationality to the appropriate booking on the Booking Summary report.
1. Download the reports you'll need
From your NightsBridge Calendar, download both:
- the 'Client' report, and
- the 'Booking Summary' report
- for the same selected date range.
Guidance on downloading reports from NightsBridge is available in our article, 'How to generate a report'.
2. Prepare your Client report
- Insert a new column next to the 'Surname' column. This will be column F
In this example, we have named the column 'Full Name'.
Video guidance on how to add or remove a row or column in Excel. - Copy and paste this formula in cell F4:
=CONCATENATE(D4," ",E4) - Use the drag tool on Excel to copy formula down to the bottom of the list.
Video guidance on how to copy a formula down a column or row in Excel
- Copy the data in column F and colomn P. These are the Full Name and Nationality columns.
Video guidance on how to copy multiple columns in Excel.
Hold down Ctrl on your keyboard when clicking to select more than one column. Make sure the cells (ie. D4, E4 and F4) are all in the same row. If you use a different column, make sure to reference the first row with data.
3. Prepare your Booking Summary Report
Open your Booking Summary report and:
- Add a new sheet.
Video guidance on how to add a new sheet in Excel. - Paste the columns you copied from your Client report (the Full Name and Nationality columns) in the new sheet you created on the Booking Summary report.
If you have duplicate clients on NightsBridge or some clients have no nationality set, this could result in blank fields
See our tutorials for more information on these topics:
4. Reference the Nationality using VLOOKUP
On your Booking Summary report,
- Insert a new column after the 'Company' column. This will then be column F.
Video guidance on how to add or remove a row or column in Excel. - Copy and paste this formula in cell F4:
=VLOOKUP(D4,Sheet1!A:B,2,FALSE) - Copy formula down the list using the drag tool.
Video guidance on how to copy a formula down a column or row in Excel
That's it!
You'll now have the nationality for your guests.