Help with Formula to Identify Partial Duplicates


I am trying to compile a formula that will look for partial (left 6 characters) matches in a column and indicate "Match" in column named "Duplicate". I am currently using the below formula however it returns "match" when there is no match.

=IF(LEFT([LAST CUSIP]@row, 6) = LEFT([LAST CUSIP]@row + 1, 6), "match", "")

the attached image shows the 2nd and 3rd row qualify as a match, but "match" is reflected for other rows also. Ultimately what I am trying to do is indicate the rows that share the first 6 characters but the last 3 characters at "XXX" so they can be filtered out.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!