3 Replies Latest reply on Jan 22, 2019 5:13 PM by Jennie Robertson

    Comparing two Excel sheets for changes

    Jennie Robertson



      I need to compare Excel sheets week over week and flag the differences. Ideally, I'd like to tie on Employee ID and isolate only the records where any of these fields have changed week over week: Email, Dept, Sub, and Manager. I don't necessarily need to know which field changed, only that one of them changed. This would save me from having to upload all employees weekly and only upload the records that have changed.


      I mocked up an example and made a couple changes - in this sample, Employee ID 12345 and 12349 have had one field change between each data set (Manager Email and Subsidiary).


      Thanks in advance!


        • 1. Re: Comparing two Excel sheets for changes
          Shinichiro Murakami

          Use wild card union ,and distinguish by Path or sheet name.

          Save two excel file under same folder and wild card union,

          And put fields on Row and "Sheet" on column.



          Union Your Data - Tableau


          To union tables using wildcard search

          1. On the data source page, double-click New Union to set up the union.
          2. Click Wildcard (automatic) in the Union dialog box.
          3. Enter the search criteria that you want Tableau to use to find tables to include in the union.For example, you can enter *2016 in the Include text box to union tables in Excel worksheets that end with "2016" in their names. Search criteria like this will result in the union of May2016, June2016, and July2016 tables (Excel worksheets), from the selected connection. In this case, the connection is called Sales, and the connection made to the Excel workbook containing the worksheets you wanted was in the quarter_3 folder in the sales directory (e.g., Z:\sales\quarter_3).
          4. Click Apply or OK to union.




          • 2. Re: Comparing two Excel sheets for changes
            Peter Fakan

            Hi Jennie,


            Have you seen the guidance on comparing excel sheets from within excel ? https://www.ablebits.com/office-addins-blog/2016/02/25/compare-two-excel-files-sheets/ - start from the section titled 'How to compare two excel sheets for differences in values'


            I'd approach this in a similar way using Tableau, but each week you would have to data > edit relationships with the new dataset to compare it with the (new) old dataset, so I think it might be a bit faster if you do it in excel.





            • 3. Re: Comparing two Excel sheets for changes
              Jennie Robertson

              Thanks! However, my entire upload process is built using Tableau for our Travel and Expense systems, both based off of one sheet from our HR system. I was hoping for a calculation to compare Email, Subsidiary, and Manager emails across the same Employee ID in two sheets.


              Like IF the Employee ID matches, but Email, Subsidiary, or Manager email does not match, then "changed" or something of that nature. I'm just not familiar enough with calculations/syntax to figure this out...

              Thank you!