Condition Formula

[kramart]

Hi,
I have this table ... this is a part of millions of records.

I have functions that show if STAT is 1 or 0, if type is E, if type is CA. This example shows that the same work 24188686 has one E and two CA. If I filter columns I can see either E or CA from the same work.

I would like to have some formula in the column COMBINE that would check in the first row : if type CA = 0 then check if there are CA's with the same WORKREF as E and if true return value 1.

The same approach for rows where E =0 to check if there are E's with the same WORKREF as CA and return value 1.

The idea is that then I can sum these four columns and filter column TOTAL to show all values =3.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]WORKREF[/TD]
[TD]STAT[/TD]
[TD]TYPE[/TD]
[TD]IF STATE =1[/TD]
[TD]IF TYPE = E[/TD]
[TD]IF TYPE = CA[/TD]
[TD]COMBINE[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]E[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]445566[/TD]
[TD]0[/TD]
[TD]CA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Hope that make sense.

Any suggestions would be much appreciated!!!

Thanks,

A

 

[inactiveuserps07]

kramart, my intuition says that, whatever it is you're trying to do, there's a more efficient way. However, given the information you've provided, and assuming the upper left-hand corner of your table is in A1, copy the formula below to F2 and drag down Column F as far as you need:

=IF(F2=0,(COUNTIFS(A:A,A2,C:C,"CA")>0)+0,IF(E2=0,(COUNTIFS(A:A,A2,C:C,"E")>0)+0,0))

If your table doesn't begin at A1 and you'd like to provide the table name or the start location, I'd be happy to adapt the formula to your specific situation.

 

[FDibbins]

Eric, what is the +0 for after the countifs?

 

[FDibbins]

the3 way I understand this, you can do it all with 1 helper column...
=(B2=1)+(C2="e")+(C2="ca")+AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

This breaks down to...
D2=--(B2=1)
E2=--(C2="e")
F2=--(C2="ca")
G2=--AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

Not sure how you get 1 in G3 (Combine, row 2)?

 

[inactiveuserps07]

Ford, the +0 is one way to turn a "TRUE" or "FALSE" into its corresponding number, 1 or 0. Other ways include adding *1 or prefacing the condition with --.

So ...

COUNTIFS(A:A,A2,C:C,"CA")>0)+0

... means "Find the number of instances where Column A contains the Work Ref from this row and Column C is 'CA.' Tell me whether it's TRUE or FALSE that this number of instances is more than 0. Then convert that logical word to a number, either 1 or 0, and put that in the cell.

 

[FDibbins]

Ford, the +0 is one way to turn a "TRUE" or "FALSE" into its corresponding number, 1 or 0. Other ways include adding *1 or prefacing the condition with --.

Yes, I know what it does, but don't see the need for it? COUNTIFS will return a number 0 or greater, so "converting to a number" seems redundant?

 

[FDibbins]

Ya know, it would help if I actually read the whole formula...
,(COUNTIFS(A:A,A2,C:C,"CA")>0)+0,
apologies lol

another way...
--(COUNTIFS(A:A,A2,C:C,"CA")>0)

 

[inactiveuserps07]

Hi, Frank. Yes, +0, *1 and -- all achieve the same thing.

 

[kramart]

Hi Erik,

Many thanks for your code. It is almost what I need. This is an example where three works have the same WORKREF but there is no E.

[TABLE="class: cms_table, align: left"]
<tbody>[TR]
[TD]WORKREF[/TD]
[TD]STAT[/TD]
[TD]TYPE[/TD]
[TD]IF STATE =1[/TD]
[TD]IF TYPE = E[/TD]
[TD]IF TYPE = CA[/TD]
[TD]COMBINE[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]24188[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]24188[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]24188[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]445566[/TD]
[TD]0[/TD]
[TD]CA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










There are some cases where there are few works with the same WORKREF number but they all are CA. I would like to take this into account and if there is no E in the column TYPE that would have the same WORKREF number as CA's then the formula should return 0.

Just to give you additional information.

This is just a simplified version of my table. There are more columns with diferent references and the outcomes in coulumns
[TABLE="class: cms_table, align: left"]
<tbody>[TR]
[TD]IF STATE =1,[/TD]
[TD]IF TYPE = E,[/TD]
[TD]IF TYPE = CA[/TD]
[/TR]
</tbody>[/TABLE]


are depending on other tables. I am using formulas vlookup, search etc. Below there is an example where outcomes in the last two columns are 0 because certain conditions wasn't met.

CA

CA

CA

E

SE

<tbody>
[TD="align: right"]6965[/TD]
[TD="align: right"]100[/TD]
[TD="width: 64, align: right"]100[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"]CA[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]

[TD="align: right"]6965[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6965[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6965[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6965[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6965[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

<tbody>
</tbody>

Hop that makes sense.

Thanks in advance,

Art

 

[kramart]

Hi Ford,

Many thanks for your input but to be hones I don't understand your code ... could you take me trough it please ?

the3 way I understand this, you can do it all with 1 helper column...
=(B2=1)+(C2="e")+(C2="ca")+AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

This breaks down to...
D2=--(B2=1)
E2=--(C2="e")
F2=--(C2="ca")
G2=--AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

Cheers,

Art