# IF + MATCH + repeated values

#### lionelcba10

##### New Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Marcelo Branco

##### MrExcel MVP
Maybe...

Pasta1
ABCD
1HourEventDisconnected atTime out
201:25:36Ping down01:25:3603:25:00
302:25:17Ping down--
403:47:50Trying to reconnect--
504:50:36Reconnected--
605:20:14Ping down05:20:1402:40:00
705:24:14Ping down--
805:28:15Ping down--
906:14:15Trying to reconnect--
1007:08:55Trying to reconnect--
1108:00:14Reconnected--
Plan8
Cell Formulas
RangeFormula
C2:C11C2=IF(AND(B2="Ping down",B1<>"Ping down"),A2,"-")
D2:D11D2=IF(ISNUMBER(C2),INDEX(A2:A\$1000,MATCH("Reconnected",B2:\$B\$1000,0))-C2,"-")

Hope this helps

M.

#### lionelcba10

##### New Member
Maybe...

Pasta1
ABCD
1HourEventDisconnected atTime out
201:25:36Ping down01:25:3603:25:00
302:25:17Ping down--
403:47:50Trying to reconnect--
504:50:36Reconnected--
605:20:14Ping down05:20:1402:40:00
705:24:14Ping down--
805:28:15Ping down--
906:14:15Trying to reconnect--
1007:08:55Trying to reconnect--
1108:00:14Reconnected--
Plan8
Cell Formulas
RangeFormula
C2:C11C2=IF(AND(B2="Ping down",B1<>"Ping down"),A2,"-")
D2:D11D2=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

##### MrExcel MVP
I hope this works for all scenarios

Pasta1
ABCD
1HourEventDisconnected atTime out
201:25:36Ping down01:25:3603:25:00
301:25:50Websocket closed--
401:25:59Trying to reconnect to websocket--
502:25:17Ping down--
603:47:50Trying to reconnect--
704:50:36Reconnected--
805:20:14Ping down05:20:1402:40:00
905:21:22Interface error--
1005:23:25#Error 47--
1105:24:14Ping down--
1205:28:15Ping down--
1306:14:15Trying to reconnect--
1407:08:55Trying to reconnect--
1508:00:14Reconnected--
Plan9
Cell Formulas
RangeFormula
C2:C15C2=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:D15D2=IF(ISNUMBER(C2),INDEX(A2:A\$1000,MATCH("Reconnected",B2:\$B\$1000,0))-C2,"-")

M.

Replies
4
Views
87
Replies
6
Views
108
Replies
1
Views
71
Replies
2
Views
122
Replies
19
Views
342

1,127,569
Messages
5,625,575
Members
416,119
Latest member
JCLLE

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back