If all values "x" in column A equal the same value in column B, write "y" in column C

[gpb237]

I am looking to write a formula that doesn't require the use of pivot tables (not opposed to VBA) that can answer this question.
If all the same values "x" in column A equal the same respective value in column B, write "y" in column C, if not, write "z".

To illustrate, take this picture for example.
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Column A [/TD]
[TD]Column B [/TD]
[TD]Column C [/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Found[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Found[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Found[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Lost[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Lost[/TD]
[TD]Incomplete[/TD]
[/TR]
</tbody>[/TABLE]

I need excel to return to me that all the Dogs have been found, not all the Cats have been found, and not all the Horses have been found. This is despite the fact that some of the Cats have been found. I am working with big data so manually inputting this will not be helpful.

Any ideas? Any advice will help. Thanks!

 

[gpb237]

Hahaha

It would end up being Lion, Incomplete

The only things that can return "Found" in Column C is if all of the same value in Column A returns found.
The only things that can return "Lost" in Column C is if all of the same value in Column A returns lost.

I don't know about you Aladin but that one would make me nervous

 

[gpb237]

Thanks Teeroy,

The formula works except for the last 4 *new* rows (Mouse and Bird). The formula returns "Lost" for those when it should be returning "Incomplete".

Thanks for the insight, sorry for the trouble.

I think the formula I gave you in Port #6 will work for that. Please test and let me know.

 

[gpb237]

Here's what I ended up doing. Seems a bit too complicated for my taste and for big data. (Waiting for calculations is the worst). Let me know if anyone can come up with something better.

=LOOKUP(COUNTIFS($A$1:$A$11,"="&A1,$B$1:$B$11,"Found")/COUNTIF($A$1:$A$11,"="&A1)+IF($B$1:$B$11="Unknown",1/3,0)+IF($B$1:$B$11="Sleeping",1/3,0),{0,2.2251E-308,0.666666666666667,1;"Lost","Incomplete","Lost","Found"})

Thanks Teeroy,

The formula works except for the last 4 *new* rows (Mouse and Bird). The formula returns "Lost" for those when it should be returning "Incomplete".

Thanks for the insight, sorry for the trouble.

 

[Teeroy]

I don't think the logic of your formula is correct.

Try:
C2: =IF(SUMPRODUCT(--($A$2:$A$16=A2),($B$2:$B$16="Found")+($B$2:$B$16="Lost"))=0,"Incomplete",LOOKUP(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Found")/COUNTIF($A$2:$A$16,"="&A2),{0,2.2251E-308,1;"Lost","Incomplete","Found"}))

as an Array Formula (Ctrl+Shift+Enter) then copy/Fill down.

 

[gpb237]

Hi Teeroy, I think you're right about my formula.

Unfortunately yours doesn't work when it looks for "Lost".

If I change row 2 from:
[table="width: 500, class: grid, align: left"]
[tr]
[td] Horse [/td]
[td] Lost [/td]
[td] Lost [/td]

[/tr]
[tr]
[td]Horse[/td]
[td]Lost[/td]
[td]Lost[/td]

[/tr]
[/table]
to:
[table="width: 500, class:grid, align: left"]
[tr]
[td]Horse[/td]
[td]Lost[/td]
[td]Lost[/td]

[/tr]
[tr]
[td]Horse[/td]
[td]Sleeping[/td]
[td]Lost[/td]

[/tr]
[/table]

Your formula returns "Lost" in Column C (like above) despite the second row's "Sleeping" status. Instead, it should return:

[table="width: 500, class: grid, align: left"]
[tr]
[td]Horse[/td]
[td]Lost[/td]
[td]Incomplete[/td]

[/tr]
[tr]
[td]Horse[/td]
[td]Sleeping[/td]
[td]Incomplete[/td]

[/tr]
[/table]

Otherwise it works!

I don't think the logic of your formula is correct.

Try:
C2: =IF(SUMPRODUCT(--($A$2:$A$16=A2),($B$2:$B$16="Found")+($B$2:$B$16="Lost"))=0,"Incomplete",LOOKUP(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Found")/COUNTIF($A$2:$A$16,"="&A2),{0,2.2251E-308,1;"Lost","Incomplete","Found"}))

as an Array Formula (Ctrl+Shift+Enter) then copy/Fill down.

 

[Teeroy]

I realised that I was expecting either a lost or found to be there. Try the following, which is a bit different

C2: =IF(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Found")>0,
LOOKUP(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Found")/COUNTIF($A$2:$A$16,"="&A2),{2.2251E-308,1},{"Incomplete","Found"}),
IF(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Lost")>0,LOOKUP(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Lost") /COUNTIF($A$2:$A$16,"="&A2),{2.2251E-308,1},{"Incomplete","Lost"}),"Incomplete"))

This is not an array formula, just hit Enter.

I broke it down by line for the first If statement to make it a bit easier to follow the logic.

 

[gpb237]

Hi, thanks for this formula. It works like a charm!

Now I have a quick question about the logic of the formula. How can I change the formula to say anything that is ["Unknown" "Found"] is also "Complete" and ["Unknown" "Lost"] is "Lost".
Is that too complicated to put into one equation?
So the scenarios for Complete would be: ["Found Found"] and ["Unknown Found"]
And the scenarios for Lost would be: ["Lost Lost"] and ["Unknown Lost"]

Thanks a lot! If that's too complicated I can just use the latest one you gave me, thanks!

I realised that I was expecting either a lost or found to be there. Try the following, which is a bit different

C2: =IF(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Found")>0,
LOOKUP(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Found")/COUNTIF($A$2:$A$16,"="&A2),{2.2251E-308,1},{"Incomplete","Found"}),
IF(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Lost")>0,LOOKUP(COUNTIFS($A$2:$A$16,"="&A2,$B$2:$B$16,"Lost") /COUNTIF($A$2:$A$16,"="&A2),{2.2251E-308,1},{"Incomplete","Lost"}),"Incomplete"))

This is not an array formula, just hit Enter.

I broke it down by line for the first If statement to make it a bit easier to follow the logic.

 

[Teeroy]

List ALL of your conditions instead of doing this piecemeal.
Sometimes it's better to change direction completely rather than build on a previous solution (which you've seen in the last post) and it's easy to get locked into a method if you keep building on a previous solution.

 

[gpb237]

Yes I understand. The first few posts that I wrote were simply not written with enough detail/clarity for the reader to understand; the lack of clarity was my mistake. However, the original question has only changed once.

I would've loved to give you all of the information in post number one but unfortunately I am doing this for work. The question changes as my boss' mind changes.

List ALL of your conditions instead of doing this piecemeal.
Sometimes it's better to change direction completely rather than build on a previous solution (which you've seen in the last post) and it's easy to get locked into a method if you keep building on a previous solution.

 

[Teeroy]

The question changes as my boss' mind changes.

Been there.

I'll have a think about the change and post again when I have time.