# Calculation to manage fields if other column entries are identical

Options
edited 02/22/21

Hello Community,

I am new to smartsheet and am trying to figure out a complex calculation which is not my strong suit. In a data sheet of 400+ rows, the data is organized by a unique identifier for multiple visits with a specific date. What I am trying to automate is a calculation to enumerate the difference in days from Visit # 1 for subsequent visits for each unique identifier.

The problem is putting together the calculation to state that if Unique Identifier is equal to others in the sheet, to subtract that date from the 1st visit to determine how many days from the original the next visit was.

Etc........................

Look forward to any help the community can provide.

Jason

• edited 02/22/21
Options
• ✭✭✭✭✭✭
Options

To pull the last visit date if the visit number is greater than 1 (we will use 0 for 1st visit):

=IF([Visit #]@row > 1, INDEX(COLLECT([Acquisition Date]:[Acquisition Date], [Unique Identifier]:[Unique Identifier], [Unique Identifier]@row, [Visit #]:[Visit #], [Visit #]@row - 1), 1)

Then we subtract the current visit:

=IF([Visit #]@row > 1, INDEX(COLLECT([Acquisition Date]:[Acquisition Date], [Unique Identifier]:[Unique Identifier], [Unique Identifier]@row, [Visit #]:[Visit #], [Visit #]@row - 1), 1) - [Acquisition Date]@row,

Then output 0 if the visit # is 1:

=IF([Visit #]@row > 1, INDEX(COLLECT([Acquisition Date]:[Acquisition Date], [Unique Identifier]:[Unique Identifier], [Unique Identifier]@row, [Visit #]:[Visit #], [Visit #]@row - 1), 1) - [Acquisition Date]@row, 0)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!