Calculation to manage fields if other column entries are identical

Jason Ouellette

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

Jason Ouellette

Paul Newcome

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)