# Finding two values ​​in a column and calculating the difference between adjacent cells

I've two columns as follows.

``````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 and Cell: A1 in cell C5, A9 - A6 in C9, etc. So basically, we have to look at column B to change the text value and subtract the corresponding values ​​in column A. Hopefully my explanation of the problem is clear. If not, let me know. I can help to understand better if possible.

+3

source to share

Could you use another column? if this solution might look like:

let D be an extra column

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

• drag formula from D2
• in C2 puts the formula: = IF (B2 = "Text B", A2-D1, "")

• drag formula from C2
+1

source

Here are two 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 where a row `Text B`

occurs immediately below another, where it will display whatever is in the column `A`

. I also changed `A2-D1`

to `A2-D2`

just for clarity; it doesn't change the results.

+1

source

In Google Sheets, this is a little tricky, but can only be done with formulas. I am assuming there is no title and text to search for `'B'`

, but rather `A:A`

is date and `B:B`

is text. Add a column `D`

and set `D1`

below:

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

```

The above formula will result in the latest date that has text `'B'`

. Now copy `D1`

and drag down. Now the result column `C`

is trivial. Enter below `C1`

and drag down.

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

```

An example of a file is here , and below is a screenshot of it. I set the column format `C`

to `duration`

and highlighted some dates in the column `A`

.

If you prefer a custom function or run a script, the tenth line of GAS can do the job as well. I personally prefer formulas, even if they are disgusting.

0

source

All Articles