I need a formula to look at a contact list and a checkbox column and return a flag if the person is

lrmerlino88886

I have a contact list for employee of the month nominations. When the employee is selected for employee of the month, a box is checked. I have another checkbox column set to flag. I need a formula that will flag the rows when a person is selected more than once for employee of the month. I have been racking my brain and tried several different options. I cannot get the flag to work properly. either it flags if the person is in the list more than once, or it flags everyone selected for EOM.

Kelly Moore

Hey @lrmerlino88886

When you say it flags if the person is in the list more than once - does that mean the person may have been nominated in a month other than this month but you only want to know if multiple nominations are occurring in the same month?

lrmerlino88886

In past months or in the same month. So their names will quite possibly be in the contact list more than once, but I want to flag when the checkbox for selected for EOM is checked more than once for the same person at any time during the year.

Kelly Moore

Gotcha.

=IF(COUNTIFS([Contact column]:[Contact column], Contact@row, EOM:EOM, 1)>1,1)

Be sure to change my column names to match your column names.

I wasn't sure if multiple years were in your list and if we needed to include criteria for the current year. Let me know if you need that.

Does the formula above work for you?

Kelly

lrmerlino88886

No, I've tried that one. It Flags all duplicate names whether they are selected or not.

Kelly Moore

which column is which? And could you post the formula you used?

lrmerlino88886

The star column will be used to choose the EOM from a list of names. The flag column is to identify if they've been chosen multiple times.

Kelly Moore

Do you have the system column Created (date)? Or the autonumber column? If only the auto-number column, are the entries entered at the top or the bottom of the sheet?

lrmerlino88886

I have both as I've been racking my brain trying to figure this formula out. New entries are at the bottom of the sheet. I've tried everything I can think of. :-/

Kelly Moore

Hey

Try this

=IFERROR(IF(AND(COUNTIFS([Employee Name]:[Employee Name], [Employee Name]@row, [Selected for Employee of the Month]:[Selected for Employee of the Month], 1) > 1, MAX(COLLECT(Created:Created, [Employee Name]:[Employee Name], [Employee Name]@row, [Selected for Employee of the Month]:[Selected for Employee of the Month], 1)) = Created@row), 1), 0)

Does it work?

Kelly