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.