CODEWITHSUNDEEP
excelexcel-formulavba
user2045361
user2045361

Reputation: 5

Subtract 2 rows based on last previous encountered instance and column match

I have a very large data set (sample below) where i need to compare (column B Host, column D Game) with the last time the same column pair value was encountered (column B Host, column D Game) and subtract the date column.

Note there will be multiple instances of this pair but I need the last matched instance relative to the current row as I have sorted the data by time.

I guess I need a macro/array to loop through and find the last match to subtract. Any ideas?

I cant upload an image to show my columns because of lack of points so pasting the data in here.

image

Date        Host                            Process        Game                Status   Delay

14:22:23    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d042.sim.df    DOWN    
14:22:54    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d045.sim.df    UP  
14:22:59    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d023.sim.df    DOWN    
14:23:04    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d111.sim.df    DOWN    
14:23:08    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d024.sim.df    DOWN    
14:23:16    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d122.sim.df    DOWN    
14:23:17    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d071.sim.df    DOWN    
14:23:17    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d118.sim.df    DOWN    
14:23:20    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d196.sim.df    DOWN    
14:23:21    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d045.sim.df    DOWN    
14:23:23    ns-mtc-gameserver-b-2.lb.df.com 172.29.101.10   game-d042.sim.df    DOWN

Do cell A12 -A1

Upvotes: 0

Views: 1079

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

You can enter this as an array formula, with Ctrl Shift Enter, and I believe it does what you want:

=$A2-(INDEX($A3:$A$12,MATCH(1,(($B3:$B$12=B2)*($D3:$D$12=D2)),0)))

EDIT:

In order to have it format as seconds you need to switch the order of the subtraction, so that you get a positive result. Excel won't format negative seconds:

=(INDEX($A3:$A$12,MATCH(1,(($B3:$B$12=B2)*($D3:$D$12=D2)),0)))-$A2

Upvotes: 1

Related Questions