SOLVED

Last update , Question

%3CLINGO-SUB%20id%3D%22lingo-sub-2162508%22%20slang%3D%22en-US%22%3ELast%20update%20%2C%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2162508%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20write%20a%20function%20in%20Excel%20in%20the%20%3CSTRONG%3Elast%20update%20column%3C%2FSTRONG%3E%20to%20define%3CBR%20%2F%3EWhich%20row%20is%20the%20most%20recent%20in%20the%20evaluation%20(evaluation%20date%20Col)%2C%20taking%20into%20account%20the%20contract%20number%20(x%2CY)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20clarity%20in%20the%20contract%20%3CSTRONG%3EX%3C%2FSTRONG%3E%2C%20the%20latest%20evaluation%20update%20is%20on%2004%2F01%2F2021%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20answer%20is%20%3CSTRONG%3Eyes%20or%20no%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22319%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3Econract%3C%2FTD%3E%3CTD%20width%3D%2275%22%3Evisit%20date%3C%2FTD%3E%3CTD%20width%3D%22104%22%3EEvaluation%20date%3C%2FTD%3E%3CTD%20width%3D%2276%22%3Elast%20update%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E01%2F01%2F2021%3C%2FTD%3E%3CTD%3E01%2F01%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E01%2F01%2F2021%3C%2FTD%3E%3CTD%3E03%2F01%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E01%2F01%2F2021%3C%2FTD%3E%3CTD%3E04%2F01%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E02%2F02%2F2021%3C%2FTD%3E%3CTD%3E02%2F02%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E02%2F02%2F2021%3C%2FTD%3E%3CTD%3E03%2F02%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E02%2F02%2F2021%3C%2FTD%3E%3CTD%3E04%2F02%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2162508%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2162563%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20update%20%2C%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2162563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F977269%22%20target%3D%22_blank%22%3E%40Ahmedb96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20that%20the%20data%20are%20in%20columns%20A%20to%20D%2C%20with%20headers%20in%20row%201%2C%20enter%20the%20following%20formula%20in%20D2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(MAXIFS(%24C%242%3A%24C%247%2C%24A%242%3A%24A%247%2CA2)%3DC2%2C%22yes%22%2C%22no%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2165017%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20update%20%2C%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165017%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20soo%20much%20friend%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20translate%20this%20function%20into%20a%20power%20query%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello guys,

 

I'd like to write a function in Excel in the last update column to define
Which row is the most recent in the evaluation (evaluation date Col), taking into account the contract number (x,Y)

 

For clarity in the contract X, the latest evaluation update is on 04/01/2021

 

The answer is yes or no

 

conractvisit dateEvaluation datelast update
x01/01/202101/01/2021 
x01/01/202103/01/2021 
x01/01/202104/01/2021 
y02/02/202102/02/2021 
y02/02/202103/02/2021 
y02/02/202104/02/2021 
4 Replies
best response confirmed by Ahmedb96 (New Contributor)
Solution

@Ahmedb96 

Assuming that the data are in columns A to D, with headers in row 1, enter the following formula in D2:

 

=IF(MAXIFS($C$2:$C$7,$A$2:$A$7,A2)=C2,"yes","no")

 

Fill down.

Thank you soo much friend

 

 

Can you translate this function into a power query? 

@Ahmedb96 

I can't help you with that; I hope someone else will step in.

@Ahmedb96 Perhaps like this?

Screenshot 2021-02-25 at 16.21.31.png

Attached file contains a PQ solution that only uses "standard" applied steps. No custom M-code required.