Filter dual rows ...

[adambc]

... but let me explain!

I have a Worksheet with over a 1000 rows ...

In column n there is a value "Active" or "Inactive" for each row ...

I want to identify rows with a value "Active", easy if IDs have only 1 row but ...

Some, but not all, IDs have 2 rows ...

Where I want to identify IDs with a value "Active" on both rows, but not those that have "Active" on one row, but "Inactive" on the other row ...

Any ideas?

Thanks ...

 

[adambc]

IMPORTANT UPDATE ...

I have a Worksheet with over a 1000 rows ...

In column n there is a value "Active" or "Inactive" for each row ...

I want to identify rows with a value "Active", easy if IDs have only 1 row but ...

Some, but not all, IDs have 2 OR MORE rows ...

Where I ONLY want to identify IDs with a value "Active" on both ALL rows, but not those that have "Active" on one row, but "Inactive" on the other ONE OR MORE row ...

 

[My Aswer Is This]

What does this mean:
I want to identify rows with a value "Active"
Identify ??
Does that mean hide the row delete the row or what?

 

[adambc]

What does this mean:
I want to identify rows with a value "Active"
Identify ??
Does that mean hide the row delete the row or what?

Sorry, wasn't clear was I?!!!

If each ID had only 1 row, I would filter on "Active" to get what I want ie hide any rows set to "Inactive" ...

But because some (but not all) IDs have 2 or more rows, each with an "Active" or "Inactive" value - and I ONLY want IDs that have every row set to "Active" - that doesn't work ...

Essentially I want to show IDs that are fully "Active" ...

Hope that clarifies?

Thanks ...

 

[Fluff]

You could insert a helper column using

=COUNTIFS(A:A,A2,N:N,"<>Active")

change col A to the column with your ID & then filter that for rows with 0

 

[adambc]

@Fluff

Like it, thanks ...

Not important, but is there a way to filter on 0 in the helper column - AND then hide duplicate rows by ID (column A in your example)?

 

[Fluff]

You could use

=AND(COUNTIFS(A:A,A2,N:N,"<>Active")=0,COUNTIFS(A$2:A2,A2)=1)

and filter on True

 

[adambc]

@Fluff

Perfect - thanks as always ...

 

[Fluff]

You're welcome & thanks for the feedback.