Data comparison in 2 excel spreadsheet


Need help from excel experts . I need to do data comparison and consolidation between 2 excel files, which have employee leave data in different formats. Also need to find out what might be missing. I am not sure if there could be formula for my request – but giving a try.

Below is a screenshot of 2 mini data sets representative of the actual & much larger data sets.

File one contains data for employee leaves taken in one row. For eg if Tom took leave from 23 December to 13 January, it will have only one row and will show the total number of days Tom was on leave.

Second file will have 2 or more rows, which are split by week. So basically for Tom’s leave between  from 23 December to 13 January it will have 4 rows.

Now comes the issue. I want to identify 2 things:

1. Data in File 1 that’s missing in File 2 (Tom’s sick leave for 1 December -highlighted below).

2. Data in File 2 that’s missing in File 1 (Ruby’s sick leave for 6 Jan – highlighted below). 

3. If leave type is different in both files for the same dates (Ruby’s leave from 9 – 13 Jan) 

The task is especially complicated because leaves are recorded differently in both the files.

Hope my explanation of the situation makes sense. If not please let me know. 

  Excel File 1      
  Leave Type Leave Start Date Leave End Date Duration Days
Tom Annual 23/12/2016 13/01/2017 12
Tom Sick 1/12/2016 1/12/2016 1
Ruby Annual 23/12/2016 29/12/2016 3
Ruby Annual 9/01/2017 13/01/2017 5
  Excel File 2      
  Leave Type Leave Start Date Leave End Date Duration Days
Tom Annual 23/12/2016 23/12/2016 1
Tom Annual 28/12/2016 30/12/2016 3
Tom Annual 4/01/2017 6/01/2017 3
Tom Annual 9/01/2017 13/01/2017 5
Ruby Annual 23/12/2016 23/12/2016 1
Ruby Annual 28/12/2016 29/12/2016 2
Ruby Sick 6/01/2017 6/01/2017 1
Ruby Sick 9/01/2017 12/01/2017 4



By: Shampi

Leave a Reply

Your email address will not be published. Required fields are marked *