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!

 

[Teeroy]

Try the following, with the ranges being adjusted per your requirement:

=IF(COUNTIFS(A2:A9,"="&A2,B2:B9,"Found")/COUNTIF(A2:A9,"="&A2)=1,"Complete","Incomplete")

A neater result could be obtained using a pivottable to summarize your data.
Assuming your columns have the Headers "Animal" and "Status" you'd set up the pivottable with Animal as a Row Label, Status as a Column Label and Count of Status in S Values.

 

[SpillerBD]

I use this type of formula. Modified to the example data, beginning in C2 and fill down.

=IF(NOT(SUMPRODUCT(--($A$2:$A$9=A2),1-($B$2:$B$9="Found"))),"Complete","Incomplete")

ARRAY FORMULA, confirm entry with CTRL+SHIFT+ENTER

I then use a Pivot Table to split out my "Completes" and "Incompletes" in a Pivot Table so I can focus on the problems.
(PS, I forget who helped get to my original formula...)

 

[gpb237]

Thank you, these formulas both work well! I really appreciate the quick response.

 

[gpb237]

Would either of you be able to help me write a formula with multiple if statements. I assume there needs to be an AND function in here someplace. This changes the question slightly to be:
[TABLE="class: cms_table_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]Found[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Found[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Found[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Lost[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Lost[/TD]
[TD]Incomplete[/TD]
[/TR]
</tbody>[/TABLE]


I thought I could write it on my own but I'm having troubles, any advice is welcome. Thanks!

 

[Teeroy]

You didn't say what you wanted the test to be. I infer that if all of animal type found display "Found", if all lost display "lost" otherwise display "incomplete". If that is correct try:

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

and copy/fill down.

 

[gpb237]

I'm sorry, I think I didn't explain myself properly the first time. Essentially I am dealing with multiple statuses but I only want two of the most important statuses to be flagged in Column C.

So I would like for all of Column A that has the status of "Found" to appear as "Found". I would like all of Column A that has the status of "Lost" to appear as "Lost". And any other combination whatsoever to appear as "Incomplete" (or False or whatever alternative).
So if we take Cat for example, Cat appears as Found, Unknown, and Sleeping- this should appear as Incomplete. Mouse appears as Unknown and Unknown -- this should also be listed as Incomplete.

For example:
[TABLE="class: cms_table_cms_table_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]Found[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Found[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD]Lost[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Unknown[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Sleeping[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Found[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Unknown[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Unknown[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]Sleeping[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]Sleeping[/TD]
[TD]Incomplete[/TD]
[/TR]
</tbody>[/TABLE]

Except assume that Column A is in a random order.

So column C would depend solely on the status of "Found" or "Lost" and nothing else. That's why I thought that combining two IF statements might be useful.

Thank you so much for your help!!

 

[Teeroy]

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

 

[Aladin Akyurek]

What happens when the following records are available?

lion, found
lion, lost

 

[Teeroy]

What happens when the following records are available?

lion, found
lion, lost

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