Formula to find the first occurrence and return the date

Connie Cochran

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, ""), ""))

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:

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

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?

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.

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

Fig 2

Fig 3

Fig 4

Fig 5

Fig 6