If then statement--highlight and/or delete record

[Buck1480]

I have the following data set:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]UniqueID[/TD]
[TD]R0A1[/TD]
[TD]Animal_ID[/TD]
[TD]TSB[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]462[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]462[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]462[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]462[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]462[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]


I need to delete all rows that have a TSB value="NA." However, I have a nested data structure. In other words, I have 5 rows of data (R0A1 = 0) tied to an animal ID with R0A1 = 1. Each animal ID has a uniqueID value associated to both the R0A1=1 and R0A1=0. See example below. The "if then" statement needs to find both the R0A1=1 and R0A1=0 associated to each unique ID and either highlight them for deletion or actually delete them (in other words remove completely the uniqueID even if 1 row under the uniqueID has a TSB value = "NA". See example with UniqueID = 7 in table above. This entire set of records tied to uniqueID 7 need to be deleted. Same for uniqueID = 8....all records associated to uniqueID 8 need to be deleted (or highlighted so I can delete them).

The issue that I've ran into is that I can select all rows with value TSB="NA"; however, this would result in some uniqueIDs having less than 6 rows associated to them (1 row for R0A1=1 and 5 rows for R0A1=0). I would greatly appreciate any assistance on this task. Thank you!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]UniqueID[/TD]
[TD]R0A1[/TD]
[TD]Animal_ID[/TD]
[TD]TSB[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]526[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

You can achieve this with a helper column

Say your data are in columns A:D, headers in row 1, values beginning in row 2
In E2 put this formula
=--(COUNTIFS(A:A,A2,D:D,"NA")>0)
copy down till the end of your data

This being done, filter column E by value = 1 and delete the visible rows.

Hope this helps

M.

 

[Buck1480]

That worked perfectly! I'm curious how exactly does that formula work? Thanks again!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.