Returning a result ONLY for unique criteria, with multiple criteria. OFFSET(), FREQUENCY(), SUMIF()

Question

Here is an overview of what I am trying to do.

i have 6 columns A is alphanumerical B is text C,D are binary data E is text F is the column I want to populate with binary data

To simplify I would like to test columns B,C,D,E against a set of criteria (each column has its own set of criteria to test against). If all B,C,D, and E are "true" I want to return a "1" in column F.

Now here comes the problem I'm having.

Basically A is an ID#, and B,C,D,E are criteria each must met to return a "1" in column F. The problem is that there are multiple rows of each ID#. It doesn't matter which ID# instance, but I only want to return ONE "1" in column F for each unique ID#.

Once a 1 is returned for a particular ID#, I don't need it to actually check the remainder of the same ID# rows, it can move on to the next ID# if need be.

I'm returning a "1" for the simplicity of the question, but eventually I want to have multiple criteria sets, and return a letter corresponding to each set instead of a "1".

Ex/

Row 1 A="1D45", B=true, C=false, D=true, E=true -- Returns F="0"

Row 2 A="1D45", B=true, C=false, D=true, E=false -- Returns F="0"

Row 3 A="1D45", B=true, C=true, D=true, E=true -- Returns F="1"

Row 4 A="1D45", B=true, C=true, D=true, E=true -- Returns F="1", BUT i want it to show 0

Row 5 A="1D45", B=true, C=false, D=true, E=false -- Returns F="0"

Row 6 A="1D45", B=true, C=true, D=true, E=true -- Returns F="1", BUT i want it to show 0

Row 7 A="1D46", B=true, C=true, D=true, E=true -- Returns F="1"

etc. etc.

Thanks a bunch

Answer 1

Your formulas are:

F1:
=--AND(B1,C1,D1,E1)

F2:
=AND(B2,C2,D2,E2)*(0=COUNTIFS(A$1:A1,A2,F$1:F1,1))

Fill down column F from F2.

In the first AND, you can replace B2 and C2 etc with your actual conditions. The last condition is actually what checks that this ID in column A has not yet got a value of 1 in column F above it.