Compare two sheets and prepare a Discrepancy Report

{ 4 Comments }

By Ashish Mathur · · · Leave a comment

In this workbook, the worksheet named Bookings shows flight booking data as per the company’s records.  The Worksheet named Vendor data shows bookings as per the vendor’s records.  There are discrepancies at three levels between these two sheets.

1. There could be PNR’s in the company records (column B of Bookings) which are not present in the vendor’s records (column C of Vendor Data)
2. There could be PNR’s in the vendor’s records (column C of Vendor Data) which are not present in the company records (column B of Bookings)
3. For PNR’s that match between two sheets, the fares could be different (column K of bookings sheet and column L of Vendor sheet)

The objective is to:

1. Create a sheet named PNR discrepancy which lists down points 1 and 2 mentioned above
2. Create a sheet named Fare Mismatches which lists down PNR wise fare mismatches between two sheets – point 3 above.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Thank you for taking the time to look at this – I’ve been trying for days now and sorry for the miscommunication, I’m a newbie sorry!

    OK, so in the example I’ve given I would like to compare the publication_title (starting at row A2) print_identifier(starting at row B2) columns in worksheet 1; with the Title (starting B2) and ISSN (starting at C2) from worksheet 2.

    I need to create a discrepancy report to show:

    The publication_titles listed on worksheet 1 and are not listed in the Titles column on worksheet 2
    The Titles listed on worksheet 2 and are not listed in the publication_titles column on worksheet 1
    The publication_tiles listed on worksheet 1 that match with the Titles on worksheet 2 but do not match on the print_identifier/ISSN columns

    I would really like the results listed as your discrepancy report is laid out – so the report would appear on worksheet3 – with columns headed Title and Status to show results with a statement like:

    Not on worksheet 1
    Not on worksheet 2
    ISSN mismatch

    Do you think this is achievable?

    I really appreciate all the help!

    Chrissie

    Reply