IF + MATCH + repeated values

[lionelcba10]

Hi, maybe some of you guys can help me out, i have the following table:

Hour	Event	Disconnected at	Time out
01:25:36	Ping down
02:25:17	Ping down
03:47:50	Ping down
04:50:50	Reconnected
05:20:14	Ping down
06:14:15	Ping down
07:08:55	Ping down
08:00:01	Reconnected

I need to write a formula that returns the amount of time this machine was disconnected, counting from the first "Ping down" event, it should look like this:

Hour	Event	Disconnected at	Time out
01:25:36	Ping down	01:25:36	03:25:00
02:25:17	Ping down	-	-
03:47:50	Trying to reconnect	-	-
04:50:36	Reconnected	-	-
05:20:14	Ping down	05:20:14	02:40:00
05:24:14	Ping down	-	-
05:28:15	Ping down	-	-
06:14:15	Trying to reconnect	-	-
07:08:55	Trying to reconnect	-	-
08:00:14	Reconnected	-	-

The problem is that i'm stuck with getting the first "ping down" value, i tried with this on C2 cell:

=IF(B2="Ping down";IF(IF.ERROR(MATCH("Ping down";B2:B$1000;0)<MATCH("Reconnected";B2:B$1000;0);A2)=TRUE;"-";A2);"-")

But doesn't work, it only returns the last "ping down".

Maybe the fourth column is not necessary and it can all be calculated on C:C but i think that exceedes my knowledge.

 

[Marcelo Branco]

Maybe...

Pasta1
	A	B	C	D
1	Hour	Event	Disconnected at	Time out
2	01:25:36	Ping down	01:25:36	03:25:00
3	02:25:17	Ping down	-	-
4	03:47:50	Trying to reconnect	-	-
5	04:50:36	Reconnected	-	-
6	05:20:14	Ping down	05:20:14	02:40:00
7	05:24:14	Ping down	-	-
8	05:28:15	Ping down	-	-
9	06:14:15	Trying to reconnect	-	-
10	07:08:55	Trying to reconnect	-	-
11	08:00:14	Reconnected	-	-
Plan8

Cell Formulas
Range	Formula
C2:C11	C2	=IF(AND(B2="Ping down",B1<>"Ping down"),A2,"-")
D2:D11	D2	=IF(ISNUMBER(C2),INDEX(A2:A$1000,MATCH("Reconnected",B2:$B$1000,0))-C2,"-")

Hope this helps

M.

 

[lionelcba10]

Maybe...

Pasta1
	A	B	C	D
1	Hour	Event	Disconnected at	Time out
2	01:25:36	Ping down	01:25:36	03:25:00
3	02:25:17	Ping down	-	-
4	03:47:50	Trying to reconnect	-	-
5	04:50:36	Reconnected	-	-
6	05:20:14	Ping down	05:20:14	02:40:00
7	05:24:14	Ping down	-	-
8	05:28:15	Ping down	-	-
9	06:14:15	Trying to reconnect	-	-
10	07:08:55	Trying to reconnect	-	-
11	08:00:14	Reconnected	-	-
Plan8

Cell Formulas
Range	Formula
C2:C11	C2	=IF(AND(B2="Ping down",B1<>"Ping down"),A2,"-")
D2:D11	D2	=IF(ISNUMBER(C2),INDEX(A2:A$1000,MATCH("Reconnected",B2:$B$1000,0))-C2,"-")

Hope this helps

M.

It works but the actual log contains more strings between the "ping down" events, my bad, it actually looks like this:

Hour	Event	Disconnected at	Time out
01:25:36	Ping down
01:25:50	Websocket closed
01:25:59	Trying to reconnect to websocket
02:25:17	Ping down
03:47:50	Trying to reconnect
04:50:36	Reconnected
05:20:14	Ping down
05:21:22	Interface error
05:23:25	#Error 47
05:24:14	Ping down
05:28:15	Ping down
06:14:15	Trying to reconnect
07:08:55	Trying to reconnect
08:00:14	Reconnected

So when i tried your formula it returned the values of every "Ping down" event, and it should be the first (the one at 01:25:36)

 

[Marcelo Branco]

I hope this works for all scenarios ?

Pasta1
	A	B	C	D
1	Hour	Event	Disconnected at	Time out
2	01:25:36	Ping down	01:25:36	03:25:00
3	01:25:50	Websocket closed	-	-
4	01:25:59	Trying to reconnect to websocket	-	-
5	02:25:17	Ping down	-	-
6	03:47:50	Trying to reconnect	-	-
7	04:50:36	Reconnected	-	-
8	05:20:14	Ping down	05:20:14	02:40:00
9	05:21:22	Interface error	-	-
10	05:23:25	#Error 47	-	-
11	05:24:14	Ping down	-	-
12	05:28:15	Ping down	-	-
13	06:14:15	Trying to reconnect	-	-
14	07:08:55	Trying to reconnect	-	-
15	08:00:14	Reconnected	-	-
Plan9

Cell Formulas
Range	Formula
C2:C15	C2	=IF(AND(B2="Ping down",COUNTIF(INDEX(B$2:B2,IFERROR(LOOKUP(2,1/(B$2:B2="Reconnected"),ROW(B$2:B2)-ROW(B$2)+1),1)):B2,"ping down")=1),A2,"-")
D2:D15	D2	=IF(ISNUMBER(C2),INDEX(A2:A$1000,MATCH("Reconnected",B2:$B$1000,0))-C2,"-")

M.