✓

Placement Form Documentation

Company Response Setup

Part 2: Connect Company Response Sheet with Master Placement Record

Now open the Google Form response sheet of the company. It will also have a tab named: 

Form responses 1

Assume the company response sheet has a Roll No column:

B = Roll No

If the Roll No is in some other column in your company form, you will need to replace B in the formula with that column.

Step 4: Create a new tab in Company response sheet

Click + at the bottom of Company response sheet.

Rename the new tab to:

Placed_Import

Step 5: Import the master roll list

Paste this formula in Placed_Import, cell A1: 

=IMPORTRANGE("MASTER_SHEET_LINK_HERE","Placed_Roll_List!A:A")

Replace MASTER_SHEET_LINK_HERE with the Google Sheet URL of the master sheet.

Example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxx/edit","Placed_Roll_List!A:A")

The first time, #REF! will appear. Click on that cell, then click: 

Allow access

Now the clean Roll Nos of the master placed students should appear in Placed_Import.

Part 3: Clean Roll No in the Company Response Sheet

Now go to the company response sheet tab: 

Form responses 1

Create helper columns on the right side of the sheet. These helper columns will clean the Roll No and then check it against the master placed student list.

Step 6: Write the header in BA1

In cell BA1, write this header: 

Clean Roll No

Step 7: Paste the formula in BA2

Paste this formula in BA2: 

=ARRAYFORMULA(IF(B2:B="","",UPPER(TRIM(B2:B))))

Here, B2:B is the Roll No column of the company response sheet.

If the Roll No column is in C, then the formula will be: 

=ARRAYFORMULA(IF(C2:C="","",UPPER(TRIM(C2:C))))

This formula cleans Roll No values by removing extra spaces and converting lowercase letters to uppercase. 

BM-025066 → BM-025066
bm-025066 → BM-025066
 BM-025066  → BM-025066

Part 4: Create the Match Column

After cleaning the company response Roll No, create one more helper column to check whether that Roll No exists in the imported master list.

Step 8: Write the header in BD1

In cell BD1, write this header: 

Master Roll Match

Step 9: Paste the formula in BD2

Paste this formula in BD2: 

=ARRAYFORMULA(IF(A2:A="","",IF(COUNTIF(Placed_Import!A:A,BA2:BA)>0,"MATCH","")))

This means:

If the Clean Roll No from the company response
is found in the master Placed_Import list
then MATCH will appear in the BD column

Now test it. If a student’s Roll No is present in the master list, then this should appear in the BD column of that row: 

MATCH

Part 5: Apply Highlighting

Now when MATCH is appearing in the BD column, apply conditional formatting to highlight the matching rows.

Step 10: Select the range

If you want to highlight the entire row, select this range: 

A2:AA1000

Step 11: Open Conditional formatting

Go to the menu: 

Format → Conditional formatting

Step 12: Apply custom formula

In Format cells if, select: 

Custom formula is

Paste this formula: 

=$BD2="MATCH"

Choose a color, for example light red or yellow.

Then click: 

Done

Final Formulas

Master file: 

=ARRAYFORMULA(IF('Form responses 1'!C2:C="","",UPPER(TRIM('Form responses 1'!C2:C))))

Company file Placed_Import: 

=IMPORTRANGE("MASTER_SHEET_LINK_HERE","Placed_Roll_List!A:A")

Company file BA2: 

=ARRAYFORMULA(IF(B2:B="","",UPPER(TRIM(B2:B))))

Company file BD2: 

=ARRAYFORMULA(IF(A2:A="","",IF(COUNTIF(Placed_Import!A:A,BA2:BA)>0,"MATCH","")))

Conditional formatting: 

=$BD2="MATCH"
Copied!
```