Overview

At Peoplesafe, data transparency is key to managing user safety. While our Nexus portal allows you to download usage reports in 3-month increments, many administrators require a consolidated Annual Report to track total usage across the entire year.

This guide walks you through merging four quarterly reports into one master file, ensuring both your numerical data (usage counts) and text data (Names, Departments, Teams) are accurately combined.

Step 1: Download your Quarterly Data from Nexus

First, you need to gather your source data.

1. Log in to Nexus.

2. Navigate to the Reports section, and open the Usage report

3. Export four separate usage reports, setting your date ranges as follows:

   • Jan-Mar

   • Apr-Jun

   • Jul-Sep

   • Oct-Dec

4. Open all four files in Excel. For ease of use, move each report into a single Excel Workbook as four separate worksheets named exactly as listed above.

Step 2: Consolidate the Numerical Usage Data

We will use Excel’s Consolidate tool to automatically add up the usage numbers for every user, even if they only appear in one or two quarters.

1. Create a new blank worksheet in your workbook and name it Annual Summary.

2. Click on cell A1.

3. Go to the Data tab and select Consolidate.

4. Set the Function to Sum.

5. Click the Reference box, navigate to the Jan-Mar sheet, and highlight the data (including the Name column and all numeric usage columns). Click Add.

6. Repeat this for the other three sheets (Apr-Jun, Jul-Sep, Oct-Dec).

7. In the "Use labels in" section, check both Top row and Left column.

8. Click OK.

Excel has now created a unique list of every user and summed their total usage for the year.

Step 3: Back-filling User Details (Teams, Depts, etc.)

The Consolidate tool only moves numbers. To bring back text-based info like Team Names or Job Titles, we use a "Nested XLOOKUP." This formula searches through all four quarters until it finds the user's information.

1. On your Annual Summary sheet, create a heading for your text column (e.g., "Team") in Column B.

2. Paste the following formula into cell B2:

=XLOOKUP($A2, 'Jan-Mar'!$A:$A, 'Jan-Mar'!B:B, XLOOKUP($A2, 'Apr-Jun'!$A:$A, 'Apr-Jun'!B:B, XLOOKUP($A2, 'Jul-Sep'!$A:$A, 'Jul-Sep'!B:B, XLOOKUP($A2, 'Oct-Dec'!$A:$A, 'Oct-Dec'!B:B, "")))) & ""

Formula Explained:

• The $A2 Lock: It always looks for the name in Column A.

• The "Nesting": If a user didn't have activity in Q1, the formula automatically checks Q2, then Q3, and so on.

• The & "": This ensures that if a field is empty, Excel shows a clean blank cell instead of a zero.

Step 4: Finalising the Report

• For multiple columns: If you need to pull in more text (e.g., Column C or Column D), simply copy the formula from cell B2 and paste it into the new columns. Because we used "Relative References" (B:B), the formula will automatically update to pull the correct data for that column.

• Locking the data: Once your report is complete, we recommend highlighting the whole sheet, selecting Copy, then Paste Special > Values. This removes the formulas and keeps the data as permanent text.