Skip to content
TheChatArchiveThe Chat Archive
grok-3-preview-02-24· xAIHugging Face LMArena Human Preference 140K

I have a Gsheet for a Service Schedule I would like to keep track of who last completed…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Mistralmistral-medium-2505’s answer.
  1. User

    I have a Gsheet for a Service Schedule I would like to keep track of who last completed a Job. In H2:H24 I have Technician 1's Names In I2:I24 I have technician 2's Names in K2:K24 I would like to keep the last input into H2:I24 in J2:J24 I have a Status based on =IF(F2>=D2, "Completed", "Due") D2 being the allocated Hrs/Mnth and F2 being the hours expended

  2. Assistant

    To track the last technician(s) who completed a job in your Google Sheet, you can use a combination of Google Sheets formulas and possibly Google Apps Script (if you want to automate the process of recording the last input). Below, I'll outline a solution that uses formulas to achieve this, and if needed, I can also provide a script-based solution for more advanced automation.

    Goal:

    • In column J (J2:J24), you want to display the name(s) of the last technician(s) who worked on a job, based on the latest input in columns H (Technician 1) and I (Technician 2).
    • The status in column K (K2:K24) is determined by the formula =IF(F2>=D2, "Completed", "Due").

    Solution Using Formulas:

    To track the last technician(s) who worked on a job, you can use a formula in column J to concatenate the names of the technicians in H and I if the job is marked as "Completed" in column K. Here's how you can do it:

    1. Add a Formula in J2: In cell J2, enter the following formula:

      =IF(K2="Completed", TRIM(CONCATENATE(H2, IF(AND(H2<>"", I2<>""), " & ", ""), I2)), "")
      

      This formula does the following:

      • Checks if the status in K2 is "Completed".
      • If "Completed", it concatenates the names in H2 (Technician 1) and I2 (Technician 2) with an " & " separator if both names are present.
      • If only one name is present, it shows just that name.
      • If the status is not "Completed", it leaves the cell blank ("").
    2. Copy the Formula Down: Copy the formula from J2 down to J24 to apply it to all rows.

    Explanation of the Formula:

    • K2="Completed": Checks if the job is marked as completed.
    • TRIM(CONCATENATE(H2, IF(AND(H2<>"", I2<>""), " & ", ""), I2)): Combines the names in H2 and I2 with an " & " separator if both are non-empty. The TRIM function ensures no extra spaces are included.
    • IF(K2="Completed", ..., ""): Ensures that the technician names are only shown if the job is completed; otherwise, the cell remains blank.

    Example Output:

    H (Tech 1)I (Tech 2)K (Status)J (Last Tech)
    JohnCompletedJohn
    AliceBobCompletedAlice & Bob
    CharlieDue
    DavidEveCompletedDavid & Eve

    Limitations of the Formula-Based Approach:

    • The formula only shows the current names in H and I when the job is marked as "Completed". It does not "remember" the last names entered if the names in H or I are later changed or cleared.
    • If you need to permanently record the names of the technicians who last worked on the job (even if H or I is later modified), you will need to use Google Apps Script to "freeze" the names in column J at the time the job is marked as "Completed".

    Advanced Solution Using Google Apps Script (Optional):

    If you want to permanently record the last technician names in column J when the status changes to "Completed" (and prevent overwriting if H or I is later changed), you can use Google Apps Script. Here's how:

    1. Open the Script Editor:

      • In your Google Sheet, go to Extensions > Apps Script.
    2. Add the Following Script:

      function onEdit(e) {
        var sheet = e.source.getActiveSheet();
        var range = e.range;
        var row = range.getRow();
        var col = range.getColumn();
        
        // Check if the edit is in the "Status" column (K, column 11) and within rows 2-24
        if (col === 11 && row >= 2 && row <= 24) {
          var status = range.getValue();
          var tech1 = sheet.getRange(row, 8).getValue(); // Column H (Tech 1)
          var tech2 = sheet.getRange(row, 9).getValue(); // Column I (Tech 2)
          var lastTechCell = sheet.getRange(row, 10);   // Column J (Last Tech)
          
          // If status is "Completed", record the technician names in column J
          if (status === "Completed") {
            var lastTech = "";
            if (tech1 && tech2) {
              lastTech = tech1 + " & " + tech2;
            } else if (tech1) {
              lastTech = tech1;
            } else if (tech2) {
              lastTech = tech2;
            }
            lastTechCell.setValue(lastTech);
          }
        }
      }
      
    3. Save and Authorize the Script:

      • Save the script and grant the necessary permissions when prompted.
    4. How It Works:

      • The script triggers automatically whenever an edit is made in the Google Sheet (onEdit trigger).
      • It checks if the edit is in column K (Status) and within rows 2-24.
      • If the status is changed to "Completed", it reads the names in H (Tech 1) and I (Tech 2) and writes them to J (Last Tech) in the same row.
      • Once the names are written to J, they will not change even if H or I is later modified.

    Notes:

    • If you want to clear the names in J when the status changes back to "Due", you can modify the script to include an else condition to clear the lastTechCell value.
    • The script assumes that the status in column K is manually updated or updated via the formula =IF(F2>=D2, "Completed", "Due"). If the status is formula-based, the script will not trigger automatically because onEdit only triggers for manual edits. In that case, you would need a different approach, such as a time-driven trigger or a custom function to periodically check the status.

    Which Solution to Use?

    • Use the formula-based solution if you are okay with the names in J being dynamically updated based on the current values in H and I.
    • Use the Google Apps Script solution if you need to permanently record the names in J at the time the job is marked as "Completed" and prevent them from being overwritten.

    Let me know if you need further clarification or help implementing either solution!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.