COL A COL B COL C COL D DUPE?
1 2 3 4/1 N
2 3 3 4/1 N
3 4 2 4/1 N
1 2 3 4/2 Y
Looking at the above example, how would I write a formula for column 5 (DUPE?) that looks at the first 3 columns and then, if COL D is the MIN Date, it does not consider a DUPE, but any dates after it, it is a dupe?
I have IF(COUNTIFS(A:A, A2, B:B, B2, C:C, C2)>1,"Y","N")
but, it is tagging the 1st one as a duplicate, also.
Have attached sample file.
Any idea?
Try:
Formula:
Please Login or Register to view this content.
Also the more general shall work, but could (?) be slower in case of larger data sets
Formula:Please Login or Register to view this content.
Last edited by Kaper; 04-05-2020 at 03:31 PM.
Best Regards,
Kaper
@Kaper Definitely slower! Never use entire column references with an array formula!
Since the OP gives 365 as their version, why not MINIFS?
Regards
Funny to note, but in 365 version it does not to be array formula.
As for MINIFS - indeed, I got used to older Excel versions and sometimes offer solutions back-compatible with say 2000 or 2003, even if more compact/elegant solution exists since … for instance Excel 2007
Last edited by Kaper; 04-05-2020 at 03:43 PM.
This appears to work! Thank you for a quick reply. Appreciate it.
What? The CSE formula you gave? Of course it does! It's just pure coincidence that the non-CSE version gives correct results with the data provided. Try changing some of the entries in column D and you'll see.Originally Posted by Kaper
Regards
Just curious, how would MINIFS work with the given solution? Is there a better solution out there? Thanks
Last edited by aetedford; 04-05-2020 at 04:14 PM.
=IF(MINIFS(D:D,A:A,A2,B:B,B2,C:C,C2)=D2,"N","Y")
With this you can safely use entire column references, unlike with the MIN(IF array formula.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks