Home

Compare data in a column and set a cell to yes if date is higher than the other

%3CLINGO-SUB%20id%3D%22lingo-sub-482524%22%20slang%3D%22en-US%22%3ECompare%20data%20in%20a%20column%20and%20set%20a%20cell%20to%20yes%20if%20date%20is%20higher%20than%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482524%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20got%20three%20columns%20with%20following%20data%20in%20it%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20355px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110239i0509B5E4F67AE69F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Untitled2.png%22%20title%3D%22Untitled2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20want%20that%20all%20unique%20Products%20in%20column%20A%26nbsp%3B%20will%20be%20set%20to%20%22Yes%22%20but%20if%20there%20is%20a%20duplicate%20in%20column%20A%20it%20schould%20compare%20the%20Date%20in%20column%20B%26nbsp%3B%20between%20those%20two%20and%20take%20the%20latest%20date%20and%20set%20it%20to%20%22Yes%22%20and%20the%20other%20one%20to%20%22No%22.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20409px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110238iAF34CCE27992E835%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Untitled.png%22%20title%3D%22Untitled.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482524%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482621%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20data%20in%20a%20column%20and%20set%20a%20cell%20to%20yes%20if%20date%20is%20higher%20than%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327137%22%20target%3D%22_blank%22%3E%40tlisobar%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482578%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20data%20in%20a%20column%20and%20set%20a%20cell%20to%20yes%20if%20date%20is%20higher%20than%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482578%22%20slang%3D%22en-US%22%3EThat%20is%20exactly%20I%20was%20looking%20for.%20Thank%20you%20so%20much.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482548%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20data%20in%20a%20column%20and%20set%20a%20cell%20to%20yes%20if%20date%20is%20higher%20than%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327137%22%20target%3D%22_blank%22%3E%40tlisobar%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(COUNTIF(%24A%242%3A%24A%244%2C%24A2)%3D1%2C%22Yes%22%2C%20IF(%24B2%3DAGGREGATE(14%2C6%2C1%2F(%24A2%3D%24A%242%3A%24A%244)*%24B%242%3A%24B%244%2C1)%2C%22Yes%22%2C%22No%22))%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
tlisobar
New Contributor

Hello Community,

 

I have got three columns with following data in it:Untitled2.png

I want that all unique Products in column A  will be set to "Yes" but if there is a duplicate in column A it schould compare the Date in column B  between those two and take the latest date and set it to "Yes" and the other one to "No".

Untitled.png

Can anyone help me?

3 Replies

@tlisobar ,

 

That could be

=IF(COUNTIF($A$2:$A$4,$A2)=1,"Yes", IF($B2=AGGREGATE(14,6,1/($A2=$A$2:$A$4)*$B$2:$B$4,1),"Yes","No"))

and drag it down

That is exactly I was looking for. Thank you so much.