ARRAYFORMULA with COUNTIF in Google Sheets

Question

We have a Google Form using which users will submit their survey. It may so happen that same user might submit the survey multiple times, at which point we only want to consider the latest response and ignore all the previous responses. The sheet has two key columns (timestamp and email) and we have created an additional column "Accepted" which is expected to have two values "0" or "1" where responses with "0" are not accepted and responses with "1" are accepted (as this is the latest submitted response).

Timestamp | Email | Accepted
2020/06/01 00:05:22 | [email protected] | 0
2020/06/01 00:35:06 | [email protected] | 1
2020/06/01 01:45:51 | [email protected] | 1
2020/06/01 02:03:40 | [email protected] | 0
2020/06/01 03:44:01 | [email protected] | 1

The first row of the Google Sheet has headers. So, the formula entered in C2 is:

=IF(COUNTIF($B$1:B2,B2)=COUNTIF($B$1:B,B2),1,0)

which works perfectly when dragged to all the rows. However, since we want to update the Column C to each new response, tried using the ARRAYFORMULA as below:

=ARRAYFORMULA(IF(COUNTIF($B$1:B2,B2:B)=COUNTIF($B$1:B,B2:B),1,0))

which returns 0 to all rows. Link to the sample Google Sheet is below: https://docs.google.com/spreadsheets/d/1qAdOIV_ZioBw41UR_WEBR_Ean5tQ0Yd_f6oLRi83fOk/edit?usp=sharing

Please help. Thanks in advance :)

Would you be willing to have an Apps Script custom formula for this? Also, considering that you want certain actions to be done when a Form is submitted, it would be appropriate to have an onFormSubmit trigger. What do you think about that? — Iamblichus
Have no idea about onFormSubmit trigger. Haven't used any of those so far :) — Prashanth JC

Iamblichus Iamblichus · Accepted Answer · 2020-06-02T13:53:35

You can accomplish this via a custom function created in Google Apps Script. To achieve this, follow these steps:

In your spreadsheet, select Tools > Script editor to open a script bound to your file.
Copy this function in the script editor, and save the project:

function GETLAST(input) {
  input = input.filter(row => row[1] !== ""); // Remove empty elements
  return input.map(row => { // Iterate through rows
    const responderRows = input.filter(el => el[1] === row[1]); // Filter by email address
    const isLast = responderRows.every(el => el[0] <= row[0]); // Check if it's last email
    return isLast ? 1 : 0
  });
}

Now, if you go back to your spreadsheet, you can use this function like any in-built one. You just have to provide the appropriate range (in this case it would be A2:B), as you can see here:

Note:

This function is using the submission time (column A) in order to find the last submission for each email address. Row order does not matter.

Reference:

Custom Functions in Google Sheets

ARRAYFORMULA with COUNTIF in Google Sheets

1 Answers

Note:

Reference: