BPPE: Labor Market Identification Data Report Support Guide

Creation date: 9/21/2023 1:22 PM Updated: 9/3/2026 7:56 AM annual annual report bppe data guide labor labor market report support guide

Support Guide for BPPE Labor Market Identification Data report

As part of BPPE reporting, schools may be required to fill out a form that looks like the below screenshot. This form comes as part of a downloadable Excel Workbook that has three spreadsheets:

Info
Dictionary
Data

The form will need to be downloaded from the BPPE website

The Setup:

First, make sure the schools fill out the yellow highlighted cells in the “Info” sheet of the report template workbook
Next we want to run 2 reports:

Student general report

Queried for actual grad date of cohort year

To determine correct date range, go into the "Dictionary" sheet of the report template workbook, and look at cell D6/D7

90-10 calc by student report with date ranges as follows:

Start date: January 1^st of 5 years prior (example: reporting in 2023, start date is 1/1/2018)
End date: December 31^st of the cohort year

The Bulk:

Export 90-10 calc by student to Excel
On 90-10 calc workbook, Create a new column:

Pre 2025: right click column S and insert a new column
Post 2026: right click column W and insert a new column

In cell S2, type in “FedLoanDebt”
Insert the following formula in cell S3

Pre 2025 Formula: =H3+I3+J3+N3+O3+P3
Post 2026 Formula: = H3+I3+J3+P3+R3+S3

Click out of that cell, then click back into it, and double click the little square on the bottom right hand corner of the cell
Create a new sheet in the 90-10 calc window
Rename the new sheet to “General Report”
Export queried General Report to CSV
Copy columns D, E, AA, AJ, AQ, and BG from the General Report CSV into the “General Report” sheet of the 90-10 calc workbook
Right click on column B, and select “insert” to insert a new column

Repeat this step 3 times

Highlight column A in the “General Report” sheet
Click Data and Text to Columns
For the first option, choose “Delimited”, and click next
In the Delimiters section, select only Comma and Space, and make sure “Treat consecutive delimiters as one” is selected, and click Next
In the Column data format section, leave only “General” selected, and click finish
Rename Cell A1 as “Last name” and cell B1 as “First name”
Do a find and replace in column G where we find “Actual Grad: “ and replace with a blank.
Click Replace All

Note: it is important that you include a space after the colon

Do a find and replace in column H where we find “SSN: “ and replace with a blank.
Click Replace All

Note: it is important that you include a space after the colon

Do a find and replace in column I where we find “Course: “ and replace with a blank.
Click Replace All

Note: it is important that you include a space after the colon

Do a find and replace in column J where we find “Hours: “ and replace with a blank.
Click Replace All

Note: it is important that you include a space after the colon

Make an additional sheet, and copy and paste cells A1 – H1 of the “Data” sheet of the BPPE report template workbook into Sheet2 A1 of the 90-10 calc workbook.
Copy the first names and last names from the General Report sheet on their respective columns in Sheet 2
Copy the data from column H of the General Report sheet into column C of Sheet2
Copy the data from column G of the General Report sheet into column D of Sheet2
Copy the data from column I of the General Report sheet into column F of Sheet2
Copy the data from column J of the General Report sheet into column H of Sheet2

The One Tricky Bit:

Click into cell E1 of Sheet2 and paste in the following formula:

Pre 2025 Formula: =VLOOKUP('General Report'!F2,'SmartShared - 90-10 Calculation'!A:S,19,FALSE)
Post 2026 Formula: =VLOOKUP('General Report'!F2,'SmartShared - 90-10 Calculation'!A:V,19,FALSE)

click out of that cell, then click back into it, and double click the little square on the bottom right hand corner of the cell
highlight all of Column E, copy it, and paste it on top of itself as values only
Do a find and replace in column E where we find “#N/A “ and replace with a “0”.
Click Replace All

Wrapping up:

Click into cell A1 of Sheet2, and add filters
Home>Sort & Filter>Filter
Sort column F by “Sort A to Z”
Check column E for “bad data” using the dropdown menu for sorting and filtering found in the right corner in Cell E1.

Entries should be a whole number
No negatives

Copy and paste the values in columns A through H into cell A2 of the “Data” sheet of BPPE Labor Market Identification Data report template workbook
Column G (CredentialType) should be set to “Diploma/Certificate” for all students

School should know what it should be set to so make sure to verify with them
Can select just the top most one, then copy and paste for the rest of the column

Column I (LengthType) type should be set to “Clock” for all students

School should know what it should be set to so make sure to verify with them
Can select just the top most one, then copy and paste for the rest of the column

Column J (SOCCode), correct code for the course can be found on the “Info” sheet. Use search feature to help in finding correct code
Columns K and L (InstitutionCode and InstitutionName) should auto populate