CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Ivan Sinigaglia
Ivan Sinigaglia

Reputation: 1072

Dynamic distribution of values in different columns

  1. We have a varying number of students that changes each semester. It is in column (A) "students". Depending on the number of students, we have more or less "teachers", which are in column (B). Therefore, they are also not fixed.

  2. These students go through six fixed modules during the semester. They must pass each module with a different teacher, so they can not repeat it. And the teachers must have a balanced number of students, not needing to be an exact, approximate distribution.

  3. Then I created the lists of students and teachers and the table with the modules. With the function "COUNTA" I know how many items I have in each list and using the function "OFFSET" I move the names of the students to first column of the "matrix" of the modules. Both lists, if they have names added at the end, will modify the matrix.

  4. What I need is to distribute the teachers in the columns of the modules, but only up to the maximum number of students, repeating the sequence as it comes to an end. For example, I got using a formula in (F2) - with Arrayformula and ISBLANK - where the eight teachers listed in column (B) are distributed in the column (F - Module 1), but it stopped at the last one. I needed this distribution to continue being repeated until the last student in column E (T1, T2, T3,...,T8, T1, T2, T3,...)

  5. In addition, I needed this distribution in the other modules too, but the following starting on a "teacher below" (one line below, only the first one), as in the example of the worksheet: In module 2, starting with teacher 2; in module 3 with teacher 3, and so on up to module 6. (In Module 2: for example: T2, T3, T4,...,T8, T1, T2, T3,...)

  6. Finalizing, it would have to be a way that when I add students in column (A) or teachers in column (B), the matrix would be dynamically adjusted.

I do not know where to go beyond what I got and any help would be very welcome. I think that through Google App Script would be more viable, I have read so far, but then I would have to start a study in that area from the beginning. But OK, if this is the way to solve this.

Google Sheet Example

Upvotes: 0

Views: 112

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34400

If a formula is preferred, in F2:

=ArrayFormula(vlookup(mod(row(indirect("a1:a"&counta(A2:A)))+transpose(ROW(indirect("a1:a"&counta(F1:1))))-2,counta(B2:B))+1,{row(B2:B)-1,B2:B},2,false))

enter image description here

Upvotes: 1

bartinmader
bartinmader

Reputation: 285

Yes, this can be done with a Google Apps Script.

In JavaScript, you could implement something along those lines:

var array = [[1, 2, 3, 4, 5, 6], [1, 2, 3, 4, 5, 6], [1, 2, 3, 4, 5, 6]];
var teacherCounter = 0;
var modules = 6;
var teachers = 8;
var students = array.length;

for (var i = 0; i < modules; i++) {
  teacherCounter = i;
  for (var j = 0; j < students; j++) {
    if (teacherCounter < teachers) {
      array[j][i] = "T" + (teacherCounter + 1);
      teacherCounter++;
    } else {
      teacherCounter = 0;
      array[j][i] = "T" + (teacherCounter + 1);
      teacherCounter++;
    }
  }
}

Which returns the following array:

array = [["T1", "T2", "T3", "T4", "T5", "T6"],["T2", "T3", "T4", "T5", "T6", "T7"], ["T3", "T4", "T5", "T6", "T7", "T8"]];

Is this kind of what you're looking for?

Upvotes: 1

Related Questions