Delete row if either of two conditions met

[rml978]

Hello,

I have timetable data that I want to refine.
I want to keep rows where students have either both morning periods off or both afternoon periods off.

ie I want to keep a row if both col C&D are blank of if both col G&H are blank.
How can I do this?

thanks,
Richard

 

[Peter_SSs]

Welcome to the MrExcel board!

Are you looking for a manual method or a macro?

 

[rml978]

Welcome to the MrExcel board!

Are you looking for a manual method or a macro?

Thanks Peter.
Whichever way is easiest!
I am not super familiar with either, so I would need pretty explicit instructions for both.

I have ~130 students with a 10 day cycle, each day on a row, so I want to not have to manually look at ~1100 rows.

Is this the info helpful?

Richard

 

[Peter_SSs]

Whichever way is easiest!

Let's try a manual approach first then. If it works the way you want and you are needing to do this a lot, we can automate it with a macro.

1. Choose a vacant column, I'm using column Z
2. Leave the first cell in the column empty and in row 2 only put this formula =AND(C2&D2<>"",G2&H2<>"")
3. Select all your data, including headings (eg A1:J1100)
4. Data ribbon tab -> In the 'Sort & Filter' group click Advanced -> Filter the list in place -> List range should be populated with your data range already -> Criteria range" Z1:Z2 (note that it includes the blank cell above the formula) -> OK
5. Select from the first visible row below the headers to the last visible row and Right click -> Delete Row
6. Data ribbon tab -> click 'Clear' in the Sort & Filter group.

Done. You should be left only with rows that are blank in C & D or blank in G & H or blank in all 4 of those columns.

 

[rml978]

Let's try a manual approach first then. If it works the way you want and you are needing to do this a lot, we can automate it with a macro.

1. Choose a vacant column, I'm using column Z
2. Leave the first cell in the column empty and in row 2 only put this formula =AND(C2&D2<>"",G2&H2<>"")
3. Select all your data, including headings (eg A1:J1100)
4. Data ribbon tab -> In the 'Sort & Filter' group click Advanced -> Filter the list in place -> List range should be populated with your data range already -> Criteria range" Z1:Z2 (note that it includes the blank cell above the formula) -> OK
5. Select from the first visible row below the headers to the last visible row and Right click -> Delete Row
6. Data ribbon tab -> click 'Clear' in the Sort & Filter group.

Done. You should be left only with rows that are blank in C & D or blank in G & H or blank in all 4 of those columns.

Amazing! Works like a charm.

Now to understand this magic... I can't say I know how it works.. if you have time, could you please explain what each step is doing?

Regarding macros, I don't have to do this process very often, so this way will suffice.

Many thanks for your help.

regards,

Richard

 

[Peter_SSs]

Amazing! Works like a charm.

Great news!

if you have time, could you please explain what each step is doing?

When used as we have here, Advanced Filter applies the formula we put in Z2 to all the rows of the table.
That formula is =AND(C2&D2<>"",G2&H2<>"")
It says
when you join what is in C2 with what is in D2 you don't get an empty string. That is there is something in C2 or D2 or both.
AND
when you join what is in G2 with what is in H2 you don't get an empty string. That is there is something in G2 or H2 or both.

So the filter shows all the rows that have something in columns C or D and something in columns G or H. That means what is hidden are rows that have nothing in C & D OR nothing in G & H (that is the ones you want to keep are hidden.

We then just delete those visibles ones you don't want and unhide the ones you do.