Evaluate only PAIRS of rows

[SueG]

GOod Morning All,

Just trying to get my head around a concept for a formula. I have huge spreadsheet, of which 90% is comprised of pairs of rows (common identifier in one field). There are instances of matching 4,6, or 8 row 'clumps'. but I only want to evaluate (and eventually delete) the pairs - I want to leave any groups of 4,6,or 8 etc. Any thoughts? I can't see how to do it without a looong IF statement. Attached is an example - the identifier is the first field. Disregard the 'total' rows - they will be evaluated later. The attached example is giving me what I want for the pairs, and the 'total' rows, but the rows highlighted in purple (4 with matching identifier), I need to say "KEEP". Thanks for any direction! This is a concept I come up against a lot - only evaluating pairs, but keeping any other records NOT having one match.

 

[NARAYANK991]

Hi ,

Can you upload your workbook ?

Narayan

 

[SueG]

Hi ,

Can you upload your workbook ?

Narayan

Sorry forgot to attach!!

 

[Tiago MF]

Hi SueG

Try:
=IF((COUNTIF(A:A,A2))=2,"delete","keep")

 

[SueG]

Awesome!! SImple and clean - you rock! Thank you!!

 

[SueG]

Hello again, I would like to build on the above solution...
If I determine that there are 5 rows that have the same value in a field, as this formula does - =IF((COUNTIF(A:A,A2))=5 - I then need to know if any of the fields in say the I column of those rows have a certain value. See attached snip. I know the formula is true for the example - but then I also want to know if any of those 5 rows contain a certain value ("retiree") in the I column for just that group of 5...

 

[eibi]

Will this do?

=IF(AND(COUNTIF(A:A,A2)=5,I2="Retiree"),"Retiree","nope")

 

[Tiago MF]

Hi

Please try the following formula:
=IF(AND(COUNTIF(A:A,A2)=5,COUNTIFS(A:A,A2,I:I,"retiree")>0),TRUE,FALSE)

You can of course change the "TRUE" and "FALSE" to whatever other results depending on how you want to mark those cells.

KR,

 

[SueG]

Will this do?

=IF(AND(COUNTIF(A:A,A2)=5,I2="Retiree"),"Retiree","nope")

Thank you! though in this case I would want all 5 rows to display "retiree" - not just the one that has that value. Ultimately I need to delete the whole group if any one record of the group has that value. I should have stated that... The post below does seem to handle this, however.

 

[SueG]

Hi

Please try the following formula:
=IF(AND(COUNTIF(A:A,A2)=5,COUNTIFS(A:A,A2,I:I,"retiree")>0),TRUE,FALSE)

You can of course change the "TRUE" and "FALSE" to whatever other results depending on how you want to mark those cells.

KR,

Thank you! This does seem to work fine!! Love it. I need to learn more about countif(s) !!