SOLVED
Home

Excel - if date is same on two columns and value in one of the column is "Inactive", then hide rows

%3CLINGO-SUB%20id%3D%22lingo-sub-876884%22%20slang%3D%22en-US%22%3EExcel%20-%20if%20date%20is%20same%20on%20two%20columns%20and%20value%20in%20one%20of%20the%20column%20is%20%22Inactive%22%2C%20then%20hide%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876884%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%2C%3C%2FP%3E%3CP%3EI%20have%2050%2C000%20rows%20of%20data%20in%20excel%20spreadsheet%20and%20I%20want%20to%20hide%20rows%20where%20the%20value%20in%20date%20column%20is%20same%20in%20other%20rows%20along%20with%20the%20value%20of%20another%20column%20is%20%22Inactive%22%3C%2FP%3E%3CP%3EI%20want%20to%20hide%20the%20blue%20text%20line%20in%20the%20below%20example%20because%20the%20date%20in%20row%202%20and%20row%203%20is%20the%20same%20and%20the%20Status%20field%20is%20%22Inactive%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E%3CU%3ESNo%2CNumber%2CClass%2CDate%2CFWM%2C%20Department%2C%20Status%3C%2FU%3E%3C%2FP%3E%3CP%3E1%2CJ89789%2CM%2C%2004%2F08%2F1999%2CSup%2CMedical%2CInactive%3C%2FP%3E%3CP%3E2%2CJ89789%2CC%2C12%2F08%2F1996%2CKer%2CFinance%2CActive%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E3%2CJ89790%2CB%2C12%2F08%2F1996%3C%2FFONT%3E%2C%3CFONT%20color%3D%22%230000FF%22%3EAgn%2CSales%2CInActive%3C%2FFONT%3E%3C%2FP%3E%3CP%3E4%2CJ89789%2CM%2C%2004%2F08%2F2005%2CAdm%2CSales%2CInactive%3C%2FP%3E%3CP%3E5%2CJ89783%2CC%2004%2F08%2F1995%2CEnt%2CMedical%2CInactive%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-876884%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876911%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20if%20date%20is%20same%20on%20two%20columns%20and%20value%20in%20one%20of%20the%20column%20is%20%22Inactive%22%2C%20t%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415444%22%20target%3D%22_blank%22%3E%40Harryy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20data%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20510px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133619iD1A53797E0A1D10B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20add%20one%20more%20helper%20column%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%20(COUNTIFS(%24D%3A%24D%2C%24D2)%26gt%3B1)*(%24G2%3D%22Inactive%22)%2C%22Hide%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E(put%20in%20first%20cell%2C%20select%20column%20till%20end%20and%20Ctrl%2BD)%3C%2FP%3E%0A%3CP%3Eand%20filter%20the%20range%20on%20%22Hide%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-877811%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20if%20date%20is%20same%20on%20two%20columns%20and%20value%20in%20one%20of%20the%20column%20is%20%22Inactive%22%2C%20t%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you%20very%20much.%20Your%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EHarry%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879685%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20if%20date%20is%20same%20on%20two%20columns%20and%20value%20in%20one%20of%20the%20column%20is%20%22Inactive%22%2C%20t%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415444%22%20target%3D%22_blank%22%3E%40Harryy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHarry%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Harryy
New Contributor

Hi There,

I have 50,000 rows of data in excel spreadsheet and I want to hide rows where the value in date column is same in other rows along with the value of another column is "Inactive"

I want to hide the blue text line in the below example because the date in row 2 and row 3 is the same and the Status field is "Inactive"

 

Example:

SNo,Number,Class,Date,FWM, Department, Status

1,J89789,M, 04/08/1999,Sup,Medical,Inactive

2,J89789,C,12/08/1996,Ker,Finance,Active

3,J89790,B,12/08/1996,Agn,Sales,InActive

4,J89789,M, 04/08/2005,Adm,Sales,Inactive

5,J89783,C 04/08/1995,Ent,Medical,Inactive

3 Replies
Highlighted
Solution

@Harryy 

For such data

image.png

you may add one more helper column with formula

=IF( (COUNTIFS($D:$D,$D2)>1)*($G2="Inactive"),"Hide","")

(put in first cell, select column till end and Ctrl+D)

and filter the range on "Hide"

@Sergei BaklanThank you very much. Your help is greatly appreciated.

 

Regards,

Harry

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies