Check for "TRUE" Multiple instances

[LewisMCT333]

Hi Everyone, I hope you are able to help me, im using Excel 2010.

I have two columns one with a 9 digit number in which is effectively a transaction reference, the column next to it has either "Failure" or "Success" as a reference to the transactions process.

300001147	SUCCESS
300001230	FAILURE
300001230	SUCCESS
300001245	SUCCESS
300001262	SUCCESS
300001263	SUCCESS
300001264	FAILURE
300001264	FAILURE
300001264	FAILURE
300001265	SUCCESS
300001266	FAILURE
300001266	SUCCESS
300001267	FAILURE
300001267	FAILURE
300001269	SUCCESS
300001270	FAILURE
300001270	FAILURE
300001270	FAILURE
300001271	SUCCESS
300001272	SUCCESS

<tbody>
</tbody>

What I need to do is establish how many of these transactions that failed, did in fact eventually succeed. I was trying to achieve this using some form of =MATCH and calling the index for the cell but then I couldn't figure out how to check for the successful cell etc etc.

It seems very complicated to me, but it also feels like it shouldn't be and im maybe not seeing it clearly any more. Can anyone help? Thanks.

Kind Regards, Lewis -

 

[Special-K99]

=IF(ISNA(MATCH(A1,IF(B$1:B$20="SUCCESS",A$1:A$20),0)),"FAILURE","SUCCESS")
Array formula, use Ctrl-Shift-Enter

and copy down column C

 

[LewisMCT333]

Hi Special-K99,

Thanks for the prompt reply, when I do as you instructed it appears to only mirror the results that already exist? It doesn't qualify whether or not a transaction number as also got the process successful attributed to it.

 

[Eric W]

Welcome to the forum. Here are 2 formulas:

	A	B	C	D
1	300001147	SUCCESS		2
2	300001230	FAILURE	x
3	300001230	SUCCESS
4	300001245	SUCCESS
5	300001262	SUCCESS
6	300001263	SUCCESS
7	300001264	FAILURE
8	300001264	FAILURE
9	300001264	FAILURE
10	300001265	SUCCESS
11	300001266	FAILURE	x
12	300001266	SUCCESS
13	300001267	FAILURE
14	300001267	FAILURE
15	300001269	SUCCESS
16	300001270	FAILURE
17	300001270	FAILURE
18	300001270	FAILURE
19	300001271	SUCCESS
20	300001272	SUCCESS

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =IF(AND(MATCH(A1,$A$1:$A$20,0)=ROW(A1),B1="FAILURE",COUNTIFS($A$1:$A$20,A1,$B$1:$B$20,"SUCCESS")),"x","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D1 {=SUM(--(FREQUENCY(IF(COUNTIFS(A1:A20,A1:A20,B1:B20,"SUCCESS")*COUNTIFS(A1:A20,A1:A20,B1:B20,"FAILURE"),MATCH(A1:A20,A1:A20,0)),ROW(A1:A20))>0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the C1 formula in, and paste it down the column. It will identify the transactions which first failed and later worked.

The formula in D1 is an array formula, confirm it with Control+Shift+Enter. It will generate the count of such transactions. It works standalone, without the need for the C1 formula. But if you use the C1 formula, you can simplify it with a simple COUNTIF formula.

Hope this helps.

 

[LewisMCT333]

Hi Eric,

You glorious human being you.

Thanks for the assistance, that works perfectly. I am not keen on being the kind of person that asks the more intelligent for help but never learns from what he was given so if you have time and would not mind doing so, could you pleas explain how that formula works. So that I can understand the premise behind it and hopefully apply that logic in future should a similar issue present itself.

Kind Regards,
Lewis

 

[Eric W]

I appreciate that you like to learn, and I'd rather impart some knowledge than just a formula.

For the first formula, I determined that you needed 3 parts to make it work. 1) Is this the first line that a given number occurs? 2) Is it a FAILURE? 3) Does this number say SUCCESS somewhere later on? For part 1, I used
MATCH(A1,$A$1:$A$20,0)=ROW(A1)
MATCH returns the first position that the search value (A1) occurs in the range. So if that position is the same as the current row, then it's the first occurrence. If your range started on row 2, this wouldn't work, since MATCH would return 1, but ROW would return 2. In that case you could adjust it like:
MATCH(A2,$A$2:$A$21,0)=ROW(A2)-ROW($A$2)+1


Part 2 is easy: B1="FAILURE"

Part 3 I used COUNTIFS:
COUNTIFS($A$1:$A$20,A1,$B$1:$B$20,"SUCCESS")
This basically says count how many times in $A$1:$A$20 does A1 occur, and in $B$1:$B$20 does SUCCESS occur? If the number is at least 1, then we know that number hit SUCCESS at some point.

Finally, I wrapped all 3 of those conditions in an AND, which requires all of them to be true, and then the IF checks the AND.

If you use the C formulas, then you can simply use
=COUNTIF($C$1:$C$20,"x")

to count how many x's are in that range.

Let me know if you have any questions, or if you want the array formula explained.

 

[LewisMCT333]

Ah Okay im with you, that makes a lot of sense and is quite a logical way to address any given problem. I usually code and I find memorising Excels specific functions to be just a little more than my brain can hold.

Thanks for the lesson that helps. I presume the array function works the same way any array function does? correct me if I am wrong!

Thanks again for your help.

 

[Eric W]

I do a lot of coding too. I find that writing long Excel formulas is like writing a program with a lot of black-box subroutines. And there's a LOT of them to keep track of! It sounds like you have some idea of array formulas, but here's a description of the D1 array formula.

=SUM(--(FREQUENCY(IF(COUNTIFS(A1:A20,A1:A20,B1:B20,"SUCCESS")*COUNTIFS(A1:A20,A1:A20,B1:B20,"FAILURE"),MATCH(A1:A20,A1:A20,0)),ROW(A1:A20))>0))

In general, a regular function performs a calculation on a single value. If A1 = 1 and A2 = 2, then =A1+10 will return 11. {=A1:A2+10} will return an array of {11,12} and at that point you'll need to apply additional functions to get the result down to a single value.

Here's how the D1 formula works, going left to right: (the columns go through R, so you many need to widen your window to see them all)

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	300001147	SUCCESS	COUNTIFS (SUCCESS)	1	COUNTIFS (FAILURE)	0	Product	0	IF/Match	0	Frequency	0	>0	FALSE	--	0	SUM	2
2	300001230	FAILURE		1		1		1		2		2		TRUE		1
3	300001230	SUCCESS		1		1		1		2		0		FALSE		0
4	300001245	SUCCESS		1		0		0		FALSE		0		FALSE		0
5	300001262	SUCCESS		1		0		0		FALSE		0		FALSE		0
6	300001263	SUCCESS		1		0		0		FALSE		0		FALSE		0
7	300001264	FAILURE		0		3		0		FALSE		0		FALSE		0
8	300001264	FAILURE		0		3		0		FALSE		0		FALSE		0
9	300001264	FAILURE		0		3		0		FALSE		0		FALSE		0
10	300001265	SUCCESS		1		0		0		FALSE		0		FALSE		0
11	300001266	FAILURE		1		1		1		11		2		TRUE		1
12	300001266	SUCCESS		1		1		1		11		0		FALSE		0
13	300001267	FAILURE		0		2		0		FALSE		0		FALSE		0
14	300001267	FAILURE		0		2		0		FALSE		0		FALSE		0
15	300001269	SUCCESS		1		0		0		FALSE		0		FALSE		0
16	300001270	FAILURE		0		3		0		FALSE		0		FALSE		0
17	300001270	FAILURE		0		3		0		FALSE		0		FALSE		0
18	300001270	FAILURE		0		3		0		FALSE		0		FALSE		0
19	300001271	SUCCESS		1		0		0		FALSE		0		FALSE		0
20	300001272	SUCCESS		1		0		0		FALSE		0		FALSE		0

<tbody>
</tbody>

Sheet1​

Because of the array function, the COUNTIFS(A1:A20,A1:A20,B1:B20,"SUCCESS") part of the formula evaluates to:
COUNTIFS(A1:A20,A1,B1:B20,"SUCCESS")
COUNTIFS(A1:A20,A2,B1:B20,"SUCCESS")
.
.
.
COUNTIFS(A1:A20,A20,B1:B20,"SUCCESS")

and the results of those individual functions are stored in an array which I show in column D here. The COUNTIFS/FAILURE works the same way, and the results are in column F.

Then I multiply those 2 arrays together, item by item, and the results are in H. If this number is greater than zero, it means that the transaction number on this row occurs with at least 1 SUCCESS and FAILURE.

Now we need to know how many unique transaction numbers there are, since a given transaction number could occur with multiple FAILUREs and at least 1 SUCCESS. So IF the number in H is >0, we do a MATCH to find the first row in the range where that number appears, otherwise we return FALSE. This is column J.

The FREQUENCY function counts how many times numbers in 1 range fall into buckets defined by a second range. The second range in this case is defined by ROW(1:20), or the numbers from 1 to 20. So the FREQUENCY essentially turns into, "How many times does each number appear?". The result is in column L.

We've now located every matching transaction number in an array, with the number of times it appears in the list in column A. We just need to count the number of non-zero entries to find the number of transactions that have at least one FAILURE and one SUCCESS.

The >0 part of the formula checks the values in L and returns TRUE or FALSE (column N), the -- part coerces TRUE/FALSE to 1/0 (Column P), and finally the SUM adds the 1s.

Fairly complicated, and a few months ago I probably wouldn't have been able to do it. But it's a matter of figuring out what steps you need to take, figuring out what function works for each step, then finding a way to put them together.

Fairly long winded, but I hope it helps!

 

[LewisMCT333]

Hey Eric, that is different to how I assumed they would work, im currently learning PHP SQL and HTML5 with some CSS. Internal company servers, im only self taught, so "best practices" like what you described there is most helpful.

I guess I need to learn how to address a problem in this kind of manner first! I appreciate you taking the time to write that out for me, its been very helpful. Consider me educated

Kind Regards,
Lewis

 

[Eric W]

I'm glad it helped. I know SQL and a little HTML, plus a lot of other languages you'll never see. But it is true, the design/planning stage is similar for all of them, and "best practices" and structured design help with all of them too.

Good luck!