Transpose headers that are checked for far left column category (Google Sheets)

Question

I have been trying this with a Transpose(Filter(),Filter()) and I can not get it to work properly.. I was wondering if there was a way to Transpose the headers from sheet1 over to a different sheet if they are checked for a certain department(C:CSpreadsheet here). This would then make the Headers a dynamic list on a separate sheet for each department similar to Job Title (B:B) on sheet1. (each department has its own sheet) Thank you in advance!

Answer 1

ALTERNATIVE SOLUTION

Instead of using formulas, what we can do is to use a script via Google Apps Script that should do the sorting of your users to their respective departments. To access the script editor directly from your spreadsheet, you can click on Extensions > Apps Script.

Some key points regarding the script:

1. I've adjusted the values from your screenshot during the replication of your case, as shown below:
2. From the screenshot as well, I've made the departments into a drop-down list; this is to ensure that the script will work.
3. Finally, looking at the script, its purpose is to detect the changes made on the Department column, and then sort the user accordingly to that department's own sheet. The automation works using the onEdit() trigger, as any changes made on the sheet will be detected, and will trigger the script automatically.

You can use this sample script for your reference to see how it works:

function onEdit(e){
  let range = e.range;
  let col = range.getColumn();
  let row = range.getRow();
  let val = range.getValue();
  let src = e.source.getActiveSheet();

  // this checks the 1st column which contains the departments
  // which will be the basis of the script to sort out the users based on their respective departments
  if (col == 1 && val != ''){
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName(src.getName());
    let target = ss.getSheetByName(val);
    let data = sheet.getRange(row,1,1,sheet.getLastColumn()).getValues();
    target.appendRow(data[0]);    // adds the user onto their new department sheet
    sheet.deleteRow(row);         // removes said user from the main sheet/their previous department's sheet
  }
}

Once you have added this script, just save it and refresh your spreadsheet so that the changes will be applied, and the script will work automatically.

OUTPUT