Help with filtering dataset

%3CLINGO-SUB%20id%3D%22lingo-sub-2164908%22%20slang%3D%22en-US%22%3EHelp%20with%20filtering%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2164908%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20dataset%20with%20roughly%2030k%20rows%20and%2018%20columns.%20Each%20row%20is%20considered%20one%20%22file%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20filter%20the%20spreadsheet%20to%20remove%20some%20of%20those%20rows%20-%20i%20am%20not%20sure%20how%20many%20that%20could%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20need%20files%20were%20the%20date%20in%20column%20M%20is%20%26gt%3B%20column%20F.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20F%20contains%20a%20date%2C%20and%20column%20M%20contains%20a%20date.%20I%20want%20to%20hide%20or%20delete%20or%20what%20ever%20is%20easier%20all%20the%20other%20rows%20in%20the%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20if%20possible%20-%20a%20version%20where%20column%20M%20is%20%26gt%3B%20column%20F%20by%203%20months%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20much%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2164908%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2165203%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20filtering%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F978222%22%20target%3D%22_blank%22%3E%40Omzabs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20add%20a%20helper%20column.%20For%20example%20in%20S2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DM2%26gt%3BF2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%20This%20column%20will%20display%20TRUE%20if%20the%20date%20in%20M2%20is%20after%20the%20date%20in%20F2%2C%20FALSE%20otherwise.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0149.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257765iE65CEE96BDD59B97%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0149.png%22%20alt%3D%22S0149.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFiltering%20for%20FALSE%20hides%20the%20rows%20where%20M%20is%20later%20than%20F%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0150.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257766iA6E2D3D21A45E8BC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0150.png%22%20alt%3D%22S0150.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20change%20the%20formula%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DM2%26gt%3BEDATE(F2%2C3)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eto%20return%20TRUE%20if%20M%20is%20more%20than%203%20months%20later%20than%20F.%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2165344%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20filtering%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165344%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Hans.%20You're%20a%20legend.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20ask%20another%20question.%20What%20if%20I%20want%20to%20do%20equal%20to%20and%20greater%20than%203%20months%20%26gt%3B%3D3%20months%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20how%20to%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2165386%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20filtering%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F978222%22%20target%3D%22_blank%22%3E%40Omzabs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChanfge%20the%20formula%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DM2%26gt%3B%3DEDATE(F2%2C3)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

I have a dataset with roughly 30k rows and 18 columns. Each row is considered one "file"

 

I need to filter the spreadsheet to remove some of those rows - i am not sure how many that could be.

 

I only need files were the date in column M is > column F.

 

Column F contains a date, and column M contains a date. I want to hide or delete or what ever is easier all the other rows in the spreadsheet.

 

Also if possible - a version where column M is > column F by 3 months

 

Can you please help?

 

Thank you, much appreciated

6 Replies

@Omzabs 

I'd add a helper column. For example in S2:

 

=M2>F2

 

Fill down. This column will display TRUE if the date in M2 is after the date in F2, FALSE otherwise.

 

S0149.png

 

Filtering for FALSE hides the rows where M is later than F:

 

S0150.png

 

You can change the formula to

 

=M2>EDATE(F2,3)

 

to return TRUE if M is more than 3 months later than F.

@Hans Vogelaar 

 

Thank you Hans. You're a legend.

 

Can I ask another question. What if I want to do equal to and greater than 3 months >=3 months?

 

Do you know how to do that?

 

Thanks

@Omzabs 

Chanfge the formula to

 

=M2>=EDATE(F2,3)

@Hans Vogelaar 

 

Thanks a milion!

@Hans Vogelaar 

 

Okay lets assume one more thing.

 

Lets say I need to all entries that are greater than 35 days? instead of months but like half a month, 14 days etc.

 

Thank you - I dont intend to trickle the info. It is just that I am finding out more and more of what I don't know.

 

Really appreciate it

@Omzabs 

For 35 days, change the formula to

 

=M2>=F2+35

 

and for 14 days, use

 

=M2>=F2+14