BPTW Pre-loaded Demographics
Matching Employee Demographics with Quantum Workplace Standard Demographics via Excel Vlookup
1) Get your employee demographics ready
In this section we’ll go through how to get your employee demographics ready for matching.
Copy and Remove Duplicates
Copy your employee demographics to a new blank sheet within Excel. You will only need Text Demographics (such as Gender or Department) and not Numeric Demographics (such as Age or Tenure).
Once you have your demographics copied over to another sheet, it is a good idea to Insert a blank Column to the Right of the demographic.
Next, run the standard Excel Remove Duplicates function by Selecting the entire demographic column and then clicking the Data tab, Remove Duplicates button. You will receive a pop-up modal confirming the column you selected, within this modal, you will want to check the “My data has headers” box before running the function. Click the OK button when you’re ready to remove duplicates. A confirmation pop-up will display how many duplicate values were found and remove, and how many unique values remain.
Repeat this step for all demographics you have.
Example Video
Within the video example, you can see the Gender, Position Level, Department, and Race/Ethnicity demographics are copied to a new blank sheet and then the Remove Duplicates function is performed for each demographic.
Once finished with this step, move on to the next section.
2) Align your employee demographics with standard demographics
In this section we’ll go through how to align your employee demographics with Quantum Workplace (QW) standard demographics.
Align
To make alignment easier, insert a column to the right of each employee demographic.
Navigate to the “How to pre-load demographics” sheet and copy a column of standard demographics (such as the Gender or Position Level demographics) and paste these in the column directly to the right of your employee demographic.
Now you can manually align the demographics next to each other. If your employee demographics match the values within QW’s standard demographics, you can go ahead and delete the 2 columns as the values already match. For demographics that don’t match, align the standard demographic to align with your employee demographic. You can delete any QW standard demographic which doesn’t align to any of your employee demographics.
Pro-Tip: you can do a A-Z sort on both the employee demographic column and the QW standard demographic to sort the columns alphabetically and make it easier to align.
Example Video
Within the video example: you can see an extra column is inserted to the right of each employee demographic. Next, QW standard demographics are copied over to the new sheet containing the employee demographics. Finally, demographics are aligned between the employee demographic and standard demographic. Unused standard demographics are deleted as well.
Once all your employee demographics are aligned to QW standard demographics, move to the next step.
3) Vlookup
In this section we’ll go through the steps of a Vlookup formula with Excel.
How to do a Vlookup in Excel
After the demographics are aligned, navigate to your main employee demographics sheet and insert a column to the right of each employee demographic needing a QW standard demographic. We’ll use this new column for our VLookup.
Before we start the VLookup formula, let’s take a quick look at the formula and the different parts:
The VLookup formula looks like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Now, let’s break it down:
- § =VLOOKUP( – this tell Excel what function/formula we are going to perform, and it starts with an opening parenthesis.
- § lookup_value, – this will be the cell of the employee demographic we want to look up. Example: B2.
- § table_array, – this will be the columns or cells of data where we want to look up the value. Note, we’ll want the table array to be an absolute reference, we simply need to add $ at the beginning of the column letter and row number. Example: Sheet2!$A$2:$B$4.
- § col_index_num, – this will be the column number of the data (the QW standard demographic) we want returned. Example: 2
- § [range_lookup]) – the last part of the formula, we’ll want to type in FALSE as we want exact matches. And then close the formula with a closing parenthesis. Example: FALSE.
The final formula would look like this: =VLOOKUP(B2,Sheet2!$A:$2:$B$4,2,FALSE)
After you’ve entered the formula, press Enter. After pressing Enter, you should see the QW standard demographic value next to your employee value. You can click and drag the right bottom corner of your first cell to copy the formula down.
Repeat the VLookup formula for each employee demographic needing the QW standard demographic.
Example Video
Within the video example, you can see we have our demographics aligned and ready. A column is inserted to the right of the employee demographic we need to perform the VLookup on. Then within the first cell, the VLookup formula is started. The first cell under Position Level is selected as the “lookup_value”. The Position Level aligned demos are selected for the “table_array” and $ is added for absolute reference. A 2 is entered for the “col_index_num” as the number 2 column is the QW standard demographic we want returned. FALSE is entered as the “[range_lookup]” as we want exact matches. After pressing Return, you can see “Executive Leve (VP and above)” is returned for the standard demographic.
Once you have successfully completed this step, go onto the next section.
4) Copy and Paste the VLookup Values
In this section we’ll go through getting the QW standard demographics to be values instead of formulas.
Copy and Paste the Value
On your main employee demographics sheet, select all the cells containing the QW standard demographic (the VLookup formula cells from step 3). Right-click and Copy these cells. Then select the top cell of the cells you selected and right-click, select Paste Values. After doing this, when you click on any of the cells which had the VLookup formula, you should see the actual value in the formula bar at the top and not the VLookup formula.
Repeat this step for all cells containing the VLookup formula.
Example Video
In the example video, you can see QW standard demographics for Position Level are selected and copied. Then the first cell (of the selected cells) is selected and values are pasted in. You can see the top cell is selected and the formula bar shows the actual value instead of the VLookup formula.
Once you have copied and pasted the values of the VLookup cells, go onto the final step!
5) Getting your file ready for upload
In this section we’ll go through the final step of getting the employee demographics file ready for upload.
Demographic Column Headers
For each of the columns where we did the VLookup to get the QW standard demographic, the first cell in the column is blank/empty. In order for the file upload to recognize our demographics, we’ll want to add the QW standard demographic column header (such as Position Level) to the first/top cell for each demographic. The standard demographic column headers can be found on the “How to pre-load demographics” sheet. If your employee demographic column headers match the QW standard demographic column headers, you can simply Cut and Paste the headers from your employee demographic columns to your QW standard demographic columns.
After you have the standard demographic headers set up, you can delete your employee demographics which were used for the VLookup as these are not needed. You can select the entire column of data by clicking on the Column Letter, then right-click and select Delete.
Example Video
In the example video, you can see the employee demographic column headers match the QW standard demographic column headers (seen on the “How to pre-load demographics” sheet) and are cut/pasted to the QW standard demographic columns. After all the column headers have been moved over, the employee demographic columns are deleted as they are not needed.