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.
Status formula assistance
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Status formula assistance
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Status formula assistance
Do you mean duplicate Acc (there are none) or duplicate ID?
Why should D5 be Active? C5 has a value...
Why should D5 be Active? C5 has a value...
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Status formula assistance
Hi,HansV wrote:Do you mean duplicate Acc (there are none) or duplicate ID?
Why should D5 be Active? C5 has a value...
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Status formula assistance
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.
=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.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Status formula assistance
Spot on...
TX a stack!!!
I must really start studying up on these formulae. I keep wanting to run to complex array structures....
TX a stack!!!
I must really start studying up on these formulae. I keep wanting to run to complex array structures....
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.