Merging Two Excel Reports

You can utilize the X-LOOKUP function in Excel to merge 2 excel files.  Each of the Excel files must have a column to "match" for the merge to process.  We recommend you utilize the Staff ID as it is included in the various tabs in SchoolFi.  

The X-LOOKUP function in Excel is as follows and the required fields are highlighted:

How to Merge the 2 Excel Files together:

For this example, we exported casual pay data from the Payroll >>  Analysis >>  Casual tab and we want to merge in the staff members contact information (created an excel from the Contacts search result view).

The first step is to create an excel file with 2 tabs and then copy the excel files that you want to merge into each of the tabs.  

The exported casual pay data from the Payroll >>  Analysis >>  Casual tab will be copied into the first tab of the excel, titled "Casuals".

The staff members contact information (created an excel from the Contacts search result view) will be copied into the second tab and this information will be merged into the first tab , titled "Addresses".

In cell K1 of the Casuals tab, I created the following formula:

• The A1 (blue) is the Staff ID column header of the tab that the second tab will be merged into
• The yellow is the Staff ID column of the tab that the second tab will be merged into 
• The Purple is the entire tab that will be merged into the first tab (you can highlight the cells)

Next, before I copy and paste the formula from cell K1, I need to add in "$" to "lock in" the cells:

Lastly, I copy the formula from K1 of the Casuals tab to each row

And now my merged file looks as follows:

Finally, after the files are merged, we recommend you highlight the entire merged tab, click Control-C (to copy) and then right-click on cell A1 and I select Paste-Special, Values.  Then you can work with the merged excel file.