find 3 times the same info to send an email

Bridor

I try to find a way that my sheet could alert someone when the name in [Nom de l’employé]@row appears at least 3 times for the same subject in [SUJET B.P.I.]@row, [SUJET E.P.I.]@row or [SUJET PONCTUALITÉ]@row.

As an example, if Angela has three times “Gomme” identified for her in [SUJET B.P.I.]@row, send me an email.

Brian_Richardson

You will need a couple of helper columns for this. Add these columns to your sheet:

• An Autonumber column called "Auto"
• A Text/number column called "Row Number" with the column formula =MATCH(Auto@row,Auto:Auto,0)
• A Checkbox column called "Third entry" with the column formula

=IF(OR(COUNTIFS([SUJET B.P.I.]:[SUJET B.P.I],[SUJECT B.P.I]@row,[Row Number]:[Row Number],<[Row Number]@row)>1,COUNTIFS([SUJET E.P.I.]:[SUJET E.P.I],[SUJECT E.P.I]@row,[Row Number]:[Row Number],<[Row Number]@row)>1,COUNTIFS([SUJET PONCTUALITÉ]:[SUJET PONCTUALITÉ],[SUJECT PONCTUALITÉ]@row,[Row Number]:[Row Number],<[Row Number]@row) >1), true)

This formula will check the box on any row where there are 2 or more previous entries with the same subject in each SUJET column.

It works by using an OR statement to see if any of these three things are true:

1. In SUJECT BPI column, are there two rows before this row that have the same subject? OR
2. In SUJECT EPI column, are there two rows before this row that have the same subject? OR
3. In SUJECT PONCTUALITE column, are there two rows before this row that have the same subject?

If any of the three things are true, then the checkbox gets checked.

Then you can setup an automation alert to send you an email when the "Third entry" checkbox gets checked.

DKazatsky2

Hi @Bridor,

You can add 1 helper column, a checkbox, that is checked when your criteria is met. Use the following formula.

=IF(OR(COUNTIFS([Nom de l’employé]:[Nom de l’employé], [Nom de l’employé]@row, [SUJET B.P.I]:[SUJET B.P.I], [SUJET B.P.I]@row) >= 3, COUNTIFS([Nom de l’employé]:[Nom de l’employé], [Nom de l’employé]@row, [SUJET E.P.I]:[SUJET E.P.I], [SUJET E.P.I]@row) >= 3, COUNTIFS([Nom de l’employé]:[Nom de l’employé], [Nom de l’employé]@row, [SUJET PONCTUALITÉ]:[SUJET PONCTUALITÉ], [SUJET PONCTUALITÉ]@row) >= 3), 1)

You can then create a workflow to send the email based on the checkbox becoming checked.

Hope this helps,

Dave

Brian_Richardson

The only problem with the formula above by @DKazatsky2 is that it will check the box for all three+ rows where they are the same, and therefore send three or more email alerts when you reach that state. If you use the Row Number based approach in my response, it will only check the last row when 3 or more rows are duplicate subjects and therefore only send 1 email.

DKazatsky2

A few things to note…

• In my testing, multiple emails were not sent out but, rather, 1 email was sent with all the changed rows included. This could change depending on timing?
• The formula provided by @Brian_Richardson does not "…only check the last row when 3 or more rows are duplicate subjects…". It will check every row from 3+, so at that point it is the same situation from an email perspective.
  • The formula provided also does not include the name comparison as part of the count.
• BOTH of our formulas are counting blank subjects so a false result is generated.

Here is a new formula that still only requires 1 additional checkbox column.

=IF(OR(COUNTIFS([Nom de l’employé]$1:[Nom de l’employé]1, [Nom de l’employé]@row, [SUJET B.P.I]$1:[SUJET B.P.I]1, [SUJET B.P.I]@row, [SUJET B.P.I]$1:[SUJET B.P.I]1, <>"") >= 3, COUNTIFS([Nom de l’employé]$1:[Nom de l’employé]1, [Nom de l’employé]@row, [SUJET E.P.I]$1:[SUJET E.P.I]1, [SUJET E.P.I]@row, [SUJET E.P.I]$1:[SUJET E.P.I]1, <>"") >= 3, COUNTIFS([Nom de l’employé]$1:[Nom de l’employé]1, [Nom de l’employé]@row, [SUJET PONCTUALITÉ]$1:[SUJET PONCTUALITÉ]1, [SUJET PONCTUALITÉ]@row, [SUJET PONCTUALITÉ]$1:[SUJET PONCTUALITÉ]1, <>"") >= 3), 1)

This formula ignores the blanks but WILL check everybox from the 3rd instance on. Be aware, this formula cannot be made into a column formula due to the Absolute References - you will need to place this in the first row and drag down.

If you would like to use the solution provided by Brian, which can be made into a column formula, be sure to add in the name portion and check for blanks.

Thanks,

Dave

Bridor

Guys Merci beaucoup ! (thank you very much in French) :)

we will do some tests with this…

you are very generous of our time to help us…

very appreciated !

Pat

Bridor

indeed it is required to consider the name of the person in [Employee Name:] row and consider generated an action when one of the subjects comes back 3 times.I corrected some small typing errors in the formula but it does not work and I can not find the error.

I try this formula in column "test"

=IF(OR(COUNTIFS([Nom de l’employé :]$1:[Nom de l’employé :]1, [Nom de l’employé :]@row, [SUJET B.P.I.]$1:[SUJET B.P.I.]1, [SUJET B.P.I.]@row, [SUJET B.P.I.]$1:[SUJET B.P.I.]1, <>"") >= 3, COUNTIFS([Nom de l’employé :]$1:[Nom de l’employé :]1, [Nom de l’employé]@row, [SUJET E.P.I.]$1:[SUJET E.P.I.]1, [SUJET E.P.I.]@row, [SUJET E.P.I.]$1:[SUJET E.P.I.]1, <>"") >= 3, COUNTIFS([Nom de l’employé :]$1:[Nom de l’employé :]1, [Nom de l’employé :]@row, [SUJET PONCTUALITÉ]$1:[SUJET PONCTUALITÉ]1, [SUJET PONCTUALITÉ]@row, [SUJET PONCTUALITÉ]$1:[SUJET PONCTUALITÉ]1, <>"") >= 3), 1)

DKazatsky2

Hi @Bridor,

It looks like your formula is not recognizing the columns - this is indicated by the fact that they are not colored (see my screenshot). This sometimes happens when you cut/paste a formula. You probably need to type it in from scratch.

Give that a try.

Dave

Bridor

Hi

with your help and coaching… it turns that this formula works well :

=IF(AND(COUNTIFS(NOM:NOM; NOM@row; BPI:BPI; BPI@row) > 2; COUNTIFS(NOM:NOM; NOM@row; EPI:EPI; EPI@row) > 2; COUNTIFS(NOM:NOM; NOM@row; PONCTUALITÉ:PONCTUALITÉ; PONCTUALITÉ@row) > 2); true)

thank you again for your time and support !

Pat