I had a situation to provide differences between 2 data sets with most readable format to business, based on existing data and differences business will make a decision on next course of action.
After multiple different experiments, I have provided the data in Excel sheet by highlighting the differences. I just used conditional formatting feature!
Considering the time invested I thought it is worth to use the Excel as template.
• Consolidate the data into single Excel sheet, sort with primary key. Make sure that table1 and table2 data is in consecutive rows
• Identify if there is any difference using simple formula (=IF(Sheet1!B3=Sheet1!B2,0,1)) in a difference sheet
o Based on this formula, we will highlight the differences in the first sheet.
• Enable Conditional Formatting on B2
o Home -> Conditional Formatting -> Manage Rules/New Rule
• Use a formula to determine which cells to format -> =Sheet2!B2=1 -> Select format
• Change Applies to value to include all cells to be formatted.
• Change the formatting to give same color to 2 rows to improve readability.
In this example, there are 3 changes which are highlighted and easy to understand! Sample is given for small data set but the same can be used for huge data…
Excel sheet is available for reference at: https://onedrive.live.com/redir?resid=4A5897AB0DE9AFDC!48987&authkey=!ADzOkxOwyiY4V08&ithint=file%2cxlsx