CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
hravicha
hravicha

Reputation: 31

Finding two values in a column and calculating difference of adjacent cells

I’ve two columns as follows.

enter image description here

A              B            C
1   1/9/2016  12:57:48 PM       Text A  
2   1/9/2016 1:04:48 PM     Text A  
3   1/9/2016 1:05:04 PM     Text A  
4   1/9/2016 1:05:14 PM     Text A  
5   1/9/2016 1:05:15 PM     Text B      A5 – A1
6   1/9/2016 1:05:38 PM     Text A  
7   1/11/2016 9:57:12 AM        Text A  
8   1/15/2016 10:42:55 AM       Text A  
9   1/15/2016 10:45:21 AM       Text B      A9 – A6
10  1/15/2016 10:58:12 AM       Text A

Query: I need the difference of Cell: A5 & Cell: A1 in the cell C5, A9 – A6 in C9 and so on.. So basically, we have to look at the Column B for a change in the text value – and subtract the corresponding column A values. Hope my explanation of the problem is understandable. If not – do let me know. I can help to understand in a better way if possible.

Upvotes: 3

Views: 291

Answers (3)

Eggsalad
Eggsalad

Reputation: 383

Here are 2 small changes to the C2 formula in @Agata Powalka's answer, which is good:

=IF(B2 = "Text B", IF(B1 = "Text B", A2, A2-D2), "")

This handles the case when a Text B row occurs immediately below another, where it will display whatever's in column A. I also changed A2-D1 to A2-D2 just for clarity; it doesn't change the results.

Upvotes: 1

Sangbok Lee
Sangbok Lee

Reputation: 2229

In Google Sheets, it's a bit tricky but can be done with formulae only. I Assumed there's no header and text to find is 'B', and A:A is date and B:B is text. Add a column D and set D1 to below:

=index(
   $A:$A, 
   index(
     transpose(split("1 "&join(" ", arrayformula(if($B:$B="B", row($B:$B), ""))), " ")), 
     countif($B$1:$B1, "B"), 
     1
   )
 )

Above formula will result the last date which has text 'B'. Now copy D1 and drag down. Now result column C is trivial. Type below on C1 and drag down.

=if(B1="B", A1-D1, "")

Sample file is here and below is its screenshot. I set the format of column C to duration and emphasized some dates on column A.

enter image description here

If you prefer custom function or running a script, dozens line of GAS can do this job too. Personally, I prefer formulae, even if it's kind of ugly.

Upvotes: 0

Agata Powalka
Agata Powalka

Reputation: 11

Could you use one more column? if so the solution may look like this:

let D be additional column

  • in D1 put formula: =A1

  • in D2 put formula: =IF(B1="Text B", A2, D1)

    • drag down formula from D2

  • in C2 put formula: =IF(B2="Text B", A2-D1, "")

    • drag down formula from C2

Upvotes: 1

Related Questions