Status formula assistance

[Rudi]

Hi,

Do I need an array for this? TX

Assign Status
If value in column C then Acc is Deactivae
If no value in column C then Acc is active
For duplicate Acc, it is only Deactive if ALL Acc's have value in C

See attached. Sheet 2 has intended result.

Acc Status.xlsx

[HansV]

Do you mean duplicate Acc (there are none) or duplicate ID?
Why should D5 be Active? C5 has a value...

[Rudi]

Do you mean duplicate Acc (there are none) or duplicate ID?
Why should D5 be Active? C5 has a value...

Hi,

Sorry...typos
I mean duplicate ID's
D5 should be Deactive

BTW: We cannot rely on the formula assuming that the ID column is sorted.

[HansV]

In D2, as an ordinary (non-array) formula:

=IF(COUNTIFS($B$2:$B$16,$B2,$C$2:$C$16,"<>")=COUNTIF($B$2:$B$16,$B2),"Deactive","Active")

Fill down from D2 to D16.

[Rudi]

Spot on...
TX a stack!!!

I must really start studying up on these formulae. I keep wanting to run to complex array structures....