Formula to find the first occurrence and return the date

Connie Cochran
Connie Cochran ✭✭✭✭
in Formulas and Functions


I'm trying to create a formula that looks at the client's name and returns the event date but if the client's name is a duplicate the return only the event date of the first occurrence, but I am getting an unparseable error. Help?

=IF(COUNTIF([Client Name]1:[Client Name]@row, [Client Name]@row) = 1, [Event Date]@row, IF(COUNTIF([Client Name]1:[Client Name]@row, [Client Name]@row) > 1, IF(ROW() = MIN(COLLECT(ROW([Client Name]1:[Client Name]@row), [Client Name]1:[Client Name]@row, [Client Name]@row)), [Event Date]@row, ""), ""))

· Share on FacebookShare on Twitter

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Connie Cochran,

    If you want the first to appear date, then you can just jump to using INDEX COLLECT:

    =INDEX(COLLECT([Event Date]:[Event Date], [Client Name]:[Client Name], [Client Name]@row), 1)

    If you want the earliest date for a client, you can use MIN COLLECT:

    =MIN(COLLECT([Event Date]:[Event Date], [Client Name]:[Client Name], [Client Name]@row))

    Sample data:

    image.png

    There seems no point checking if there is more than 1 occurrence of the client here - either formula will give you the only result available.

    Hope this helps, but if I've misunderstood something or you have any problems/questions, let me know.

    Regards,

    Nick

    · Share on FacebookShare on Twitter
  • Connie Cochran
    Connie Cochran ✭✭✭✭

    THANK YOU!!!! Is there a way to have it look at the same columns on my archive sheet to ensure that I am capturing the earliest date from both?

    · Share on FacebookShare on Twitter
  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    I think you could do another MIN COLLECT to get the earliest date from your archive sheet (I would add in an IFERROR and blank return in case the client doesn't appear in the archive if they're new or similar) and then enclose both MIN COLLECTS inside a MIN - this would then give you the absolute earliest.

    I'd probably test this by doing the columns separately and then combining so you can see the results are correct before doing an all in 1.

    · Share on FacebookShare on Twitter
  • DylBlake
    DylBlake ✭✭✭

    I've struggled with this problem before and found a solution today. I wanted to be able to label the first occurrence without using any of the [Column A]$1:{Column A]@row syntax because Smartsheet doesn't allow that to be turned into a column formula. Also, I don't like Smartsheet's Auto Number columns because my solutions often have rows moved around, deleted, etc. which makes these columns not reliable as an index or row counter. However, you can follow the steps below to solve both of these issues and reliably get back whether a value in a given column is the first occurrence or not.

    I typically don't post but think this one is worth a post. There's a ton of applications of this. Fig 1 is my sample sheet. Fig 2-5 show the formulas. Steps below

    1. Set up an "Auto Number" column.
    2. Set up a dynamic row count using the match function (Fig 2)
    3. Set up another match function on the column you are looking to identify the first occurrence of a value in (Fig 3)
    4. If these two match columns are equal, you are looking at the first occurrence (Fig 4)
      1. Fig 5 shows the occurrences of "Groceries" and "Fuel" in adjacent cells in the column.
      2. Fig 6 shows where I move the first occurrence of "Fueld to row 4.

    Fig 1

    0.PNG

    Fig 2

    1.PNG

    Fig 3

    2.PNG

    Fig 4

    3.PNG

    Fig 5

    5.PNG

    Fig 6

    6.PNG

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!