Calculation to manage fields if other column entries are identical

Options
Jason Ouellette
edited 02/22/21 in Formulas and Functions

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

Answers

  • Jason Ouellette
    edited 02/22/21
    Options
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!