How to Compare Two Hotel Rooming Lists in Excel

How do you compare two hotel rooming lists in Excel?

Put both files in one workbook and work in this order: normalize the names and dates on both sheets, pick a key that cannot move, check that key for duplicates, find the unmatched rows on both sides, and only then compare the fields on the rows that paired up.

The order is the whole method. Most comparisons go wrong before a single formula is written, because someone sorts both files by name, puts them side by side, and starts reading across — which works right up until one list has a row the other does not, and every row below it is compared against the wrong reservation.

A rooming-list audit is looking for three things: reservations the hotel does not have, reservations the hotel has that you did not send, and rows where the two of you disagree about a date, a room type or a name. Everything below is in service of telling those three apart from the fourth thing, which is the two files simply being written differently.

What do the two lists actually look like?

Here is a small fictional example — a block at the invented Meridian Harbor Hotel — small enough to check by eye, which is the point: you should be able to see what the formulas are going to say before you write them.

What you sent. This is the rooming list in the layout most templates use, including the free one on this site: guest names split into first and last, and dates written unambiguously. There is a confirmation number column too, left out here because every cell in it is empty — that is the column the hotel fills in and sends back.

The fifth row is a TBD room — held and paid for, but not yet assigned to anyone.

What came back. A property management system usually exports one name column, surname first, in capitals, with its own confirmation number on every row.

Four room nights are missing and every single name is written differently. Only one of those two facts matters.

Which column should you match on?

Match on the confirmation number when both files have one, and on the guest name when they do not — which, on the first list a hotel sends back, is usually the case.

The confirmation number is the better key by a distance. It is the hotel’s own identifier for the reservation, it is unique, and no amount of re-spelling a name will break it. The catch is that it only exists on one side at first. You left that column blank when you submitted, because the number is what the hotel gives you in return. So the first comparison of a booking cycle has no shared confirmation number to key on at all, and the name is what you have. Once you have filed their numbers against your rows, every comparison after that can use them — which is a good reason to file them the day they arrive.

Do not put the arrival date in the key. It is tempting: name plus arrival feels more unique than name alone. But a moved arrival date is one of the things you are hunting, and if the date is part of the key, the reservation whose date moved stops matching. It then shows up twice — once as missing from their list, once as an extra on it — and you have turned one correction into two phantom reservations. In the example above, keying on name and arrival would report Priya Nadar as both a dropped room and an unexpected one. Keep the key to the name. Compare the dates afterwards, as fields.

How do you turn two different name formats into one key?

With a helper column on each sheet that rewrites both name formats into the same one. Your sheet concatenates; the hotel’s sheet flips the comma.

Say your list is on a sheet called Yours with headers in row 1 and data in rows 2 to 6, and the hotel’s is on a sheet called Hotel with data in rows 2 to 5. On Yours, in G2:

=IF(TRIM(A2&B2)="","",LOWER(TRIM(A2)&" "&TRIM(B2)))

That gives priya nadar for row 2, and an empty string for the unnamed row 6 — which is deliberate, and dealt with further down.

On Hotel, in F2:

=IF(ISNUMBER(FIND(",",A2)),LOWER(TRIM(MID(A2,FIND(",",A2)+1,LEN(A2)))&" "&TRIM(LEFT(A2,FIND(",",A2)-1))),LOWER(TRIM(A2)))

NADAR, PRIYA becomes priya nadar; a name with no comma in it is passed through unchanged apart from case and stray spaces. Both keys now agree, and BRENNAN, TYLER has stopped being a discrepancy.

Two honest notes on that formula. The lower-casing is for your eyes rather than the formulas — COUNTIFS and MATCH both ignore letter case already, so Tyler Brennan would have matched TYLER BRENNAN regardless; making the column readable is what earns it. And the comma flip is not a general-purpose name parser. It does not handle a slash format like BRENNAN/TYLER, and a suffix defeats it in both directions: BRENNAN, TYLER JR flips to tyler jr brennan, which will not equal tyler brennan jr, while stripping suffixes to fix that would merge a Smith Jr and a Smith Sr into one person, which is worse. Middle names and single-word names break it too. None of those appear in the example, and if they appear in your file, take those rows out and pair them by hand. A key you trust on four hundred rows and check on six is a good outcome; a key you assume works on all four hundred is how a guest loses a room.

Why check for duplicate keys before you look anything up?

Because a lookup does not tell you when it had a choice. XLOOKUP with its default search mode starts at the first item and stops at the first row that matches; MATCH with a match type of 0 is documented to find the first value exactly equal to what you asked for. Neither warns you that a second row would have done just as well.

So before any lookup, count the keys. On Yours, in H2:

=IF(G2="","",COUNTIFS($G$2:$G$6,G2))

Anything above 1 is a row that a formula cannot resolve for you. There are two ordinary reasons for it, and both are real reservations rather than errors:

• The same guest staying twice — two separate stays, often either side of a gap, legitimately sharing one name. Pair them by their dates, by hand.
• Two different people with the same full name — rarer, and the case that makes a name an unsafe identifier in principle. If you have a department or team column, use it to tell them apart; if you do not, this is what the confirmation numbers are for on the next round.

Run the same count on the hotel’s sheet. A duplicate that exists on one side and not the other is itself informative.

What do you do about rooms that have no guest name yet?

Take them out of the name match entirely and reconcile them as a count.

An unnamed room has nothing to key on, and Excel will not help you here: a COUNTIFS criterion that is blank does not count blank cells, and a blank lookup value does not find other blanks either. So TBD rows do not match, do not report as duplicates, and land silently in your unmatched pile looking exactly like reservations the hotel dropped. Rows where somebody has typed the word TBD do the opposite — they all key to tbd and collide with each other.

The rule is simple. Filter the unnamed rows out of the keyed comparison, count how many you sent, count how many unnamed or placeholder rows the hotel confirmed, and compare those two numbers on their own. In the example, you sent one and the hotel confirmed none, which is a genuine finding — three room nights you are holding with nothing to show for them.

How do you find the rows that are on one list and not the other?

With COUNTIFS run in both directions — and treat what comes back as a list of candidates, not a list of conclusions.

On Yours, in I2:

=IF(G2="","no name - reconcile by count",IF(COUNTIFS(Hotel!$F$2:$F$5,G2)=0,"Not on hotel list","Matched"))

On Hotel, in G2, the mirror image:

=IF(F2="","no name - reconcile by count",IF(COUNTIFS(Yours!$G$2:$G$6,F2)=0,"Not on your list","Matched"))

Running both directions is not optional. The first tells you what the hotel is missing; the second tells you what the hotel has that you never sent — a duplicate booking, or a guest who booked around your block and should be counted in your pickup rather than left as a surprise.

In the example, all four named rows on both sides come back Matched, including Priya Nadar — whose arrival moved. That is the key doing its job: her row is still one reservation, and the date difference is now a field comparison rather than a pair of phantoms. The only unmatched row is the TBD one, handled by count.

Before you call an unmatched row missing, check the sharers. Your list has one row per room, keyed on the lead occupant. A hotel export often has one row per person. So a shared room can turn up as an extra row on the hotel’s list under the second guest’s name — and if the hotel entered the second guest as the lead, you get a false missing row on your side at the same time. They are the same room. Look at your second-occupant column before you ask a hotel to add a reservation it already has.

How do you compare the rows that did match?

Pull the hotel’s values alongside yours and compare them cell by cell. On Yours, in J2 and K2:

=XLOOKUP(G2,Hotel!$F$2:$F$5,Hotel!$B$2:$B$5,"no match")

=IF(OR(G2="",J2="no match"),"",IF(J2=C2,"","Arrival differs"))

Repeat for departure and room type against the hotel’s columns C and D. Leaving out XLOOKUP’s fourth argument returns #N/A on a miss instead of a readable answer, which is why it is filled in here.

XLOOKUP is not in every Excel. Microsoft’s own page lists Excel 2016 and 2019 among the supported versions and then states plainly, a little further down, that it is not available in either of them. If you are on one of those, swap it for:

=IFERROR(INDEX(Hotel!$B$2:$B$5,MATCH(G2,Hotel!$F$2:$F$5,0)),"no match")

MATCH and COUNTIFS both go back to Excel 2016, which means the parts of this method that keep you out of trouble — the duplicate check and the two-way unmatched check — work in any version you are likely to be handed. Only the convenience of the lookup depends on having a recent one.

What does the finished comparison say?

Three findings out of five rows, and the room-night arithmetic closes.

That last line is the check worth doing every time. If the room nights on the two sides differ by an amount your findings do not account for, you have missed something — and it is better to find that out from arithmetic than from a guest at the front desk.

Is it a formatting difference or a real change?

A formatting difference is the same fact written two ways. A change is the two of you disagreeing about a fact. The test is whether normalizing the format makes it go away.

BRENNAN, TYLER against Tyler Brennan disappears the moment both sides are written the same way, so it was never a finding. 2027-03-04 against 2027-03-05 does not disappear under any formatting, so it is real.

But real does not mean you know who is wrong. A date mismatch tells you the two records disagree and nothing more. The hotel may have re-keyed the arrival; equally, the guest may have moved their flight and told a team lead who never told you, in which case the hotel’s row is correct and yours is stale. The same goes for a shortened first name: John where you wrote Jonathan needs to match the ID he presents at the desk, and your own file is as likely to be carrying the nickname as theirs. Confirm the intended value, then correct whichever side is wrong. Sending a hotel a correction list built on the assumption that you are always right is how a correct reservation gets broken.

Room types deserve the same caution in reverse. A hotel returning NQQ where you wrote Double Queen is almost certainly its own code for your room type rather than a downgrade — check what that code sits against on the rest of their file before you file it as a change.

What do you send the hotel once you know?

Only the rows that need to change, dated, with the previous value and the requested value side by side — and keep a copy for yourself.

Do not send a fresh full list unless the property asks for one. Some do, and their process depends on it; most would rather have a short marked-up change list they can key in minutes than a 200-row attachment they have to diff against last week’s version first. Ask once, then hold to whichever they said.

Keep your own record of what you sent either way. The rooming list change log on this site is a CSV built for exactly that — one dated line per requested change, with a column for what the hotel came back and said. It is what turns “I think we told them about the Mar 5 arrival” into a date, and it is the baseline you compare against the next time the hotel returns a file.

One thing a change log is not: a cancellation. Leaving a row off a later file does not cancel a reservation, and no hotel should treat it as one. Cancel in writing, with the confirmation number, and record the reply.

When should you stop doing this by hand?

When the file is long enough that the checking costs more than the checking is worth — which, for most blocks, is somewhere around the second or third time you do it.

Everything above is the same work the free rooming list audit on this site does for you. You give it your list and the file the hotel sent back, both as CSV or Excel, and it lines them up, reads the two name formats as one person, groups what genuinely differs by type, and gives you a corrections file and an editable email to send. It runs in your browser: the files are not uploaded, there is nothing to sign up for, and nothing is saved anywhere afterwards.

It is honest about its limits, and so is this page. It reads CSV and Excel, not a PDF or a scan of a faxed page. It compares names, dates, room types, confirmation numbers and roommates — not rates, which it does not look at. It keeps nothing, so there is no history to come back to. And like any automatic matcher, it has to make a choice when the same name appears twice with no confirmation number to separate them, which is why the duplicate check above is worth running whether you do the comparison by hand or not.

Where Blocks itself goes further is repetition rather than cleverness: it keeps the list you actually sent as the baseline, so a later edit of your own does not read as a hotel error; it takes the scanned and faxed lists; it holds a review you can walk away from; and it starts an audit straight from the hotel’s emailed attachment. Every correction it finds is proposed for you to confirm — nothing is written to a reservation until you say so. If that is the shape of your problem, the rooming-list audit page covers it. If you just need these two files reconciled today, the tool above, or the formulas above, will do it.