Macro or Function to delete rows if both criteria are not met

**nrsChristine** · 11-09-2012, 06:53 PM

I have a 48K row report from which I need to exclude accounts that did not order BOTH Item 1 and Item 2. There are anywhere from one to twenty entries per account, but only some of these have both items. Example attached. Is there a macro I can run if I define name for the item range? There are only the two items on the report.Excel Example.xlsx

**djapigo** · 11-09-2012, 08:27 PM

Hi nrsChristine,

I might have a 2-step solution for you without the use of macros...

1. I'm assuming the accounts that you are talking about are the invoice numbers... if that is the case, then use this formula in col K and copy all the way to the rest (there are a couple of COUNTIFS, so you might see it kinda slow... just be patient) once the calculating is done, I suggest you copy, paste special value to get rid of this formula... this simply looks for Items 1 & 2 within the same invoice... you will get either 1s or blanks
=IF(COUNTIFS($A$2:$A$62,A2,$I$2:$I$62,"Item 1")*COUNTIFS($A$2:$A$62,A2,$I$2:$I$62,"Item 2")=0,"",1)

2. Once you are done with step 1 which I suggest you copy paste special values to get rid of the formula, then highlight column K... press F5 (Go To function), press Special (at the bottom)... choose Constants and uncheck numbers... this will highlight only the "blank" items... then right-click somewhere inside the highlighted range, choose Delete, Entire Row...

Practice this first on your sample spreadsheet that you provided.

I think F5 (Go To function) is just not used enough...

Let me know if this doesn't work...

Later,
Dennis

**nrsChristine** · 11-12-2012, 01:36 PM

Hi, Dennis,

This worked really, really well. The unique idenifier was in a different column, but I modified the formula you provided and it accomplished exactly what I needed. Thank you ever so much for your help. I will proudly use the F5/Special function in the future.
Christine

**nrsChristine** · 11-12-2012, 02:27 PM

Hi, Dennis,

I have a follow-up question. How would I modify this CountIfs formula to ask Excel to count within the months that each Pt ID received both items? Original CountIfs is: =IF(COUNTIFS($A$2:$A$32048,A2,$I$2:$I$32048,"Item 1")*COUNTIFS($A$2:$A$32048,A2,$I$2:$I$32048,"Item 2")=0,"",1). How could I add one more limitation on it by asking "only if Item 1 and Item 2 per Month/Column D for each Pt Id/Column G" in the original example?

**djapigo** · 11-12-2012, 06:36 PM

Hi Christine,

I'm glad it worked... try this revised formula... hopefully it also works...
=IF(COUNTIFS($A$2:$A$32048,A2,$I$2:$I$32048,"Item 1",$D$2:$D$32048,D2,$G$2:$G$32048,G2)*COUNTIFS($A$2:$A$32048,A2,$I$2:$I$32048,"Item 2",$D$2:$D$32048,D2,$G$2:$G$32048,G2)=0,"",1)

I simply added the new criterias that you wanted into each COUNTIFS formula... one for col. D and another for col. G. I don't know if you understand how the formula works but these are what you call array formulas. If you take a smaller range (I suggest less than 10 items) then you can really experiment on how this works... what the first COUNTIFS formula creates is an array of TRUE or FALSE (ie 1 or 0)... that meets the 4 criterias, for example on the first record the formula asks count how many records that meet col. A = 2978908, col. I = "Item 1", col. D = Jun-12, and col. G = 1... which is 1 (this record) then the second COUNTIFS asks almost the same question… count how many records that meet col. A = 2978908, col. I = "Item 2", col. D = Jun-12, and col. G = 1… since there are no records with Item 2 along with the other 3 criterias, then this COUNTIFS formula will give us 0…
Since we multiply these 2 COUNTIFS, we get 1 * 0 = 0… and according to our IF statement, 0 will return “” (blank)…

So you can imagine if it does meet both Item 1 and Item 2, then we get 1*1=1 which is not =0, then the IF statement will return 1…

And that’s how the formula works… and paired with the magical F5… hopefully you will get what you want…

Let me know how this goes… sorry for the long explanation...

Take care,
Dennis

**nrsChristine** · 11-12-2012, 07:11 PM

This worked! Your explanation was quite helpful. I'm attempting to learn these formulas, but of course going about it the hard way. Once I saw the formula you recommended, it did make sense after some puzzling. I greatly appreciate your help on this.