SOLVED

Find earliest date based off criteria/duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-1363836%22%20slang%3D%22en-US%22%3EFind%20earliest%20date%20based%20off%20criteria%2Fduplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363836%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20looking%20to%20find%20the%20earliest%20date%20(Due%20date%2C%20Column%20D)%20for%20each%20account%20(Column%20A).%20For%20example%2C%20I'd%20like%20to%20pull%20the%20earliest%20Due%20Date%20for%20%22Deer%22%20which%20would%20be%204%2F22%2F20%20and%20pull%20it%20into%20Column%20C.%20How%20would%20I%20do%20that%3F%20Thanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1363836%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364930%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20earliest%20date%20based%20off%20criteria%2Fduplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364930%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657996%22%20target%3D%22_blank%22%3E%40alexandrabiorka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20Office%202019%20or%20365%2C%20you%20may%20use%20MINIFS%20function%20like%20this...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMINIFS(%24D%242%3A%24D%2413%2C%24A%242%3A%24A%2413%2CA2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20is%20not%20the%20case%2C%20you%20may%20try%20the%20following%20Array%20Formula%20which%20requires%20confirmation%20with%20Ctrl%2BShift%2BEnter%20instead%20of%20Enter%20alone%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMIN(IF(%24A%242%3A%24A%2413%3DA2%2C%24D%242%3A%24D%2413))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERefer%20to%20the%20attached%20for%20more%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364976%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20earliest%20date%20based%20off%20criteria%2Fduplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657996%22%20target%3D%22_blank%22%3E%40alexandrabiorka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%2C%3C%2FP%3E%0A%3CP%3Evariant%20which%20works%20practically%20on%20any%20version%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24D%3A%24D%2CAGGREGATE(15%2C6%2C1%2F(%24A%242%3A%24A%2413%3D%24A2)*ROW(%24A%242%3A%24A%2413)%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20variant%20which%20works%20only%20on%20recent%20versions%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%40SORT(FILTER(%24D%242%3A%24D%2413%2C%24A%242%3A%24A%2413%3D%24A2)%2C%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1365009%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20earliest%20date%20based%20off%20criteria%2Fduplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1365009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B-%20thank%20you%20so%20much!%20That%20worked%20great%20for%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1365011%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20earliest%20date%20based%20off%20criteria%2Fduplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1365011%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1365053%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20earliest%20date%20based%20off%20criteria%2Fduplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1365053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657996%22%20target%3D%22_blank%22%3E%40alexandrabiorka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20the%20Best%20Response%2FAnswer%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, I'm looking to find the earliest date (Due date, Column D) for each account (Column A). For example, I'd like to pull the earliest Due Date for "Deer" which would be 4/22/20 and pull it into Column C. How would I do that? Thanks! 

5 Replies
Highlighted

@alexandrabiorka 

 

If you have Office 2019 or 365, you may use MINIFS function like this...

=MINIFS($D$2:$D$13,$A$2:$A$13,A2)

 

If that is not the case, you may try the following Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone

=MIN(IF($A$2:$A$13=A2,$D$2:$D$13))

 

Refer to the attached for more details.

 

 

 

 

Highlighted

@alexandrabiorka 

In addition,

variant which works practically on any version

=INDEX($D:$D,AGGREGATE(15,6,1/($A$2:$A$13=$A2)*ROW($A$2:$A$13),1))

and variant which works only on recent versions

=@SORT(FILTER($D$2:$D$13,$A$2:$A$13=$A2),,1)
Highlighted

@Subodh_Tiwari_sktneer - thank you so much! That worked great for me.

Highlighted
Highlighted
Best Response confirmed by alexandrabiorka (Occasional Contributor)
Solution

@alexandrabiorka 

 

You're welcome! Glad it worked as desired.

 

Please take a minute to accept the post with the proposed solution as the Best Response/Answer to mark your question as Solved.