Removing Conditional Data

Eden

Hi, our company wants to select two best employees using Smartsheet, now we have three requirements,

1. For repeated [Creat by], only the latest data will be kept；

2. If [Selection 1]=[Selection2] the ballot is also invalidated；

3. Count the number of valid selections. Can you please help me to create this formula?

Thanks in advance!!!

jmyzk_cloudsmart_jp

Hi @Eden

There may be a more straightforward solution, but here is what I solved.

I used some real names in the demo below to test how this solution works.

Determine the data to use

Please look at the second sheet in the image below.

First, the Not Equal column checks if Selection 1 and 2 are unequal.

• [Not Equal] = =IF([Selection 1]@row <> [Selection 2]@row, 1, 0)

Second, the Rankeq column determines if a row, whose [Not Equal] is true has the latest data by [Creat by]

• [Rankeq]=(IF([Not Equal]@row, RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Creat by]:[Creat by], [Creat by]@row, [Not Equal]:[Not Equal], 1), 0))) 
  • I assumed the new data would be added at the top of the rows and used Row ID auto-number to rank.
  • The formula means to rank the [Row ID] of the same [Creat by] whose [Not Equal] is true.

Finally, using the [Rankeq] value, we can determine which rows we can use.

• [Use] =IF(Rankeq@row = 1, 1)

Count the number of selections that meet the condition

Please look at the first sheet in the image below.

The sheet lists the names of selection candidates in the [Name] column.

The [Count] column counts the number of valid selections ([Use] is checked) in the second sheet.

• [Count] =COUNTIFS({Selection 1}, Name@row, {Use}, 1) + COUNTIFS({Selection 2}, Name@row, {Use}, 1)
  • {Selection 1},{Use} and {Selection 2} ranges are the ranges in the second sheet.
  • The formula means to count the number of valid ({Use}, 1) selections in the Selection 1 and 2 columns with the same Name as Name@row.

List the Best two automatically in the Sheet Summary field

The rest of the columns, Match, Rankeq, Tie, Rankeq+Tie, and Rank, are used to list the Best two automatically in the Sheet Summary field.

Since those use complicated formulas, please ignore them if you are happy with checking the number in the [Count] column to determine the best two by yourself. (If you are interested in how they work, please check the column formula of the published dashboard below.)

https://app.smartsheet.com/b/publish?EQBCT=772f234228be4c18bd1c50a7c4966934