VLOOKUP Help

This is going to be the general idea. You will want to replace the name of the column that you use for the date of the observation. I also didn't know the name of the third observation type or the time period.<\/p>

=IF([Observation Type]@row = \"Safety\", WORKDAY([Observation Date]@row, 2), IF([Observation Type]@row = \"Opportunity to Improve\", WORKDAY([Observation Date]@row, 14), IF([Observation Type]@row = \"Other Type\", WORKDAY([Observation Date]@row, 365), \"//www.santa-greenland.com/community/discussion/comment/\")))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107920,"type":"question","name":"How to remove text to only leave numbers","excerpt":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)) which works just fine, but for some reason the Conditional Formatting does not recognize the calculated output as a number to highlight…","snippet":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row)…","categoryID":322,"dateInserted":"2023-07-21T15:21:47+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":96691,"lastUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3379907571,"url":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","format":"Rich","lastPost":{"discussionID":107920,"commentID":386451,"name":"Re: How to remove text to only leave numbers","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386451#Comment_386451","dateInserted":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-21T15:35:25+00:00","dateAnswered":"2023-07-21T15:30:02+00:00","acceptedAnswers":[{"commentID":386449,"body":"

Try wrapping it in a VALUE()<\/p>

=VALUE(LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107888,"type":"question","name":"Filter distinct values only into new column","excerpt":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only give me the unique values of [(Cells Linked from TE Tracker)] From what I've seen, there is no direct function that can do this, but has anyone…","snippet":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only…","categoryID":322,"dateInserted":"2023-07-20T22:29:01+00:00","dateUpdated":null,"dateLastComment":"2023-07-22T05:31:39+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T05:32:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163466,"lastUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T05:32:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3379897240,"url":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","format":"Rich","lastPost":{"discussionID":107888,"commentID":386535,"name":"Re: Filter distinct values only into new column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386535#Comment_386535","dateInserted":"2023-07-22T05:31:39+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T05:32:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-22T05:31:23+00:00","dateAnswered":"2023-07-21T02:06:24+00:00","acceptedAnswers":[{"commentID":386312,"body":"

Hi @sawuzie<\/a> <\/p>

<\/p>

There's no direct way to do this. However, you can add a checkbox helper column to identify duplicates. You can use the formula =IF(COUNTIF([Cells Linked from TE Tracker]:[Cells Linked from TE Tracker], [Cells Linked from TE Tracker]@row) > 1, 1, 0). This will add a check in the duplicates column for all values which appear more than once. You can then add the Distinct column with the formula =IF([Duplicate]@row=0, [Cells Linked from TE Tracker]@row). The Distinct column will only have the distinct values listed.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">