IF + MATCH + repeated values

lionelcba10

New Member
Joined
Feb 2, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hi, maybe some of you guys can help me out, i have the following table:

HourEventDisconnected atTime out
01:25:36Ping down
02:25:17Ping down
03:47:50Ping down
04:50:50Reconnected
05:20:14Ping down
06:14:15Ping down
07:08:55Ping down
08:00:01Reconnected

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:

HourEventDisconnected atTime out
01:25:36Ping down01:25:3603:25:00
02:25:17Ping down--
03:47:50Trying to reconnect--
04:50:36Reconnected--
05:20:14Ping down05:20:1402:40:00
05:24:14Ping down--
05:28:15Ping down--
06:14:15Trying to reconnect--
07:08:55Trying to reconnect--
08:00:14Reconnected--

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.
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Feb 2, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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:

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

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
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top