The goal of this task is to understand which fields of specialism (Front End, Back End, Full Stack, N/A) within an organisation, what technologies do each engineer know?

I.E "Oh look, across our Engineering team, 35 out of 75 FE Engineers know Python, and 42 out of 45 BE Engineers know Java"

Therefore, I have 2 sheets of data.

Sheet 1 contains a list of all software engineers in a business, the technologies that they know, and the engineer's specialism (Front End, Back End, Full Stack, N/A).

Sheet 2 contains a filtered/sanitized list of technologies, and the count for the engineers that have noted that down as a technology that they know/have some experience with, and is where I'm trying to bring this final piece of data in.

I'm 99% the way there already, but I just can't figure out how to limit the count based off of their specialism.

I now have managed to sanitize that data down to the next 4 tables. Note - Quantity currently is just a simple count of the number of engineers that have written down this particular technology and is not filtered by stack preference (see attached photo also).

What I need is for the quantity columns (E, H, K, N) to only be specific to the engineer's stack (Front End, Back End, Full Stack, N/A).

The formula (or script) should refer to the cell next to the quantity for the name of the technology, but also only include if the engineer's stack preference is 'Front End' for example.

In summary something like: Count number of engineers with 'front end' as their specialism where technology is equal to 'terraform' (or 'ansible' or 'bash' etc etc etc).

The App Script & formula I have used to get a list of sanitized data (excludes duplicates and excludes 'banned words' that were entered that don't relate to a technology name) is:

=unique(filter(AllTechnologies('Form Responses 1'!O2:O275), NOT(COUNTIF(R2:R, AllTechnologies('Form Responses 1'!O2:O275)))))

Where 'AllTechnologies' is:

function AllTechnologies(input) {
  var input = input.toString();
  var inputSplit = input.split(" ");
  Logger.log(inputSplit);
  inputSplit = inputSplit.toString();

  var punctuationless = inputSplit.replace(/[.,\/#!$%\?^&\*;:{}=\-_`~()]/g," ");
  var finalString = punctuationless.replace(/\s{2,}/g," ");
  finalString = finalString.toLowerCase();
  return finalString.split(" ");
}

Thanks for any assistance in advance.

CodePudding user response：

If Sheet1 had this table,

In Sheet2

A1: Datavalidation: =Sheet1!B2:B

A2:

=ARRAYFORMULA(QUERY({UNIQUE(FLATTEN(SPLIT(Sheet1!C2:C6,", ",1,1)))},"Where Col1 is not null"))

This creates a unique list of all languages after splitting by ,

B2:

=ARRAYFORMULA(MMULT(IF(REGEXMATCH(TRANSPOSE(Sheet1!C$2:C$5), ".*\Q"&A2:A9&"\E.*")*(TRANSPOSE(Sheet1!B$2:B$5)=A1),1,0),SEQUENCE(ROWS(Sheet1!C$2:C$5))^0))

This creates the count based on DV in A1

CodePudding user response：

A script solution could be like this:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('Sheet1');
  var sh1_data = sh1.getDataRange().getValues().slice(1);
  var sh2 = ss.getSheetByName('Sheet2');

  populate_columns_DN(sh1_data, sh2);
  populate_columns_AB(sh1_data, sh2);
}

function populate_columns_DN(sh1_data, sh2) {

  var stacks = {
      'Front End':  'FE Technology',
      'Back End':   'BE Technology',
      'Full Stack': 'FS Technology',
      'N/A':        'N/A Technology',
    }

  // make the object {'stack tech1': [name1, name2], 'stack tech2': [name1, name3], ...}
  var obj = {};
  for (let [name, stack, tech] of sh1_data) {
    stack = stacks[stack];
    let techs = AllTechnologies(tech);
    while(techs.length) {
      let key = stack   techs.pop();
      if (key in obj) obj[key].push(name); else obj[key] = [name];
    }
  }

  // get data from the sheet2
  var [header, ...data] = sh2.getDataRange().getValues();

  // populate the columns (change the data)

  const modify_data = col => {
    let stack = header[col];
    for (let i in data) {
      let tech = data[i][col];
      let key = stack   tech.toLowerCase();
      if (key in obj) data[i][col 1] = obj[key].length;
    }
  }

  modify_data(3);  // FE -- column E
  modify_data(6);  // BE -- column H
  modify_data(9);  // FS -- column K
  modify_data(12); // NA -- column N

  // put the table back on the sheet2
  sh2.getDataRange().setValues([header, ...data]);

}

function populate_columns_AB(s1_data, sh2) {

  // make obj {tech1:[name1, name2,...], tech2: [name2, name3, ...], ...}
  var obj = {};
  for (let [name, stack, tech] of s1_data) {
    let techs = AllTechnologies(tech);
    while(techs.length) {
      let tech = techs.pop();
      if (tech in obj) obj[tech].push(name); else obj[tech] = [name];
    }
  }

  // make an array for A and B columns
  var cols_ab = [];
  for (let tech in obj) cols_ab.push([ tech, obj[tech].length ]);

  // puth the array on the sheet2
  sh2.getRange(2,1,cols_ab.length,2).setValues(cols_ab);
}

If the columns A, D, G, J, M on the sheet 2 are identical the code can me modified. It can populate column A and then copy its contains to columns D, G, J, M.

In this code the columns D-M can contain different sets of techs (which makes sense, probaly). For example: