Let me share with you real case study. One of the exercises I have completed with my client recently was checking Prophet model point files ("MPF") produced from a new tool - to find out if there are any differences from the existing MPF:
STEP 1: Import MPF into Excel
 |
| Figure 1: New MPF data (labeled as "data1") |
 |
| Figure 2: Existing MPF data (labeled as "data2") |
To compare new and existing MPF, import the MPF into Excel (note: MPF are in comma delimited format) and organize the data as per Figure 1 & 2 below. If you examine "data1" sheet and "data2" sheet carefully, you will find out that the field sequences for these 2 sets of MPF are different. For example, the first field of Sheet "data1" is "Field1", whereas for Sheet "data2" is "Field2".
STEP 2: Setup Comparison Sheet
 |
| Figure 3: Copy "data1" Field Header to Sheet "Compare" |
Insert a new sheet and rename it to "compare". Copy the field headers from Sheet "data1" and paste them in Row 5, as shown in Figure 3 (you can use field headers from Sheet "data2" if you would like to make Sheet "data2" as the comparison base).
 |
| Figure 4: Copy "data1" Field Header to Sheet "Compare" |
Copy the field headers from Sheet "data2" and paste them in Row 1. Below each Sheet "data2" field header, key in the column name (A, B, C, ... either upper case or lower case), as shown in Figure 4. The labels in Row 2 represent the column name for Sheet "data2" field headers.
 |
| Figure 5: Look-up column name for Sheet "data2" field headers |
In row 3, use HLOOKUP to look-up the column name from "data2" sheet for the corresponding field name stated in row 5. For example, the formula input in Cell A3 is "=HLOOKUP(A5,$A$1:$AI$2,2,FALSE". Sounds a bit confusing? Well, the "c" in Figure 5 above means that "Field1" is located in Column C in Sheet "data2".
STEP 3: Build Excel Formula
 |
| Figure 6: Compare the first record of Field 1 |
In Cell A6, compare the value of first record of Field 1 - if the values are the same for both Sheet "data1" and Sheet "data2", return 0 as cell value (and 1 vice versa). You can do the comparison in the normal way you use Excel - for example, the formula you will create in Cell A6 is "=IF(data1!A2=data2!C2,0,1)".
 |
| Figure 7: Build Excel formula |
Instead of showing "0" in Cell A6, now you are going to change value to "=IF(data1!A2=data2!C2,0,1)" as shown in Figure 7. How? Let me share with you the most important trick of this article - you change the formula to '="=IF(data1!"&A2&"2=data2!"&A3&"2,0,1)"' and cell A6 will have the the value as shown above.
 |
| Figure 8: Copy and paste value the formula built |
Copy Cell A6 and paste in Row 6 for all column with field headers in Row 5. Copy all cells with formula in Row 6 and paste value. Now the formula in Cell A6 has been converted from '="=IF(data1!"&A2&"2=data2!"&A3&"2,0,1)"' to "=IF(data1!A2=data2!C2,0,1)". But why Cell A6 doesn't show "0" instead? Don't worry, you need to do one more step to get Cell A6 showing "0".
 |
| Figure 9: Convert Excel formula |
If you configured your Excel for Prophet previously, you can use "Refresh" found in "Add-Ins" tab. Otherwise, you can press F2 and followed by Enter for each cell in Row 2 to convert the "written" formula to "real" formula, as shown in Figure 9 above.
STEP 4: Copy Formula to Remaining Cells
 |
| Figure 10: Complete Comparisons |
Copy Row 2 and paste in the remaining rows below Row 6 (as per no. of records of Sheet "data1"). Normally, it is recommended to add a total in Row 4 so that you will know which field contains inconsistent values. Conditional formatting is a good way to indicate which field have 1 or more records with different values.
