I have two snapshots of data in big tables on two different sheets. Basically the sheets have identical content but the numbers are from different moments of time. I need to compute the delta between these moments of time.

How to easily compute delta (e.g. subtract each cell on sheet 1 from sheet 2 and store result on respective cell on sheet 3)? Non-numeric cells have identical content and should be copied as-is.

I'd prefer something that works with LibreOffice Calc but being able to do this with MS Excel is acceptable.

  • "e.g. subtract each cell on sheet 1 from sheet 2 and store result on respective cell on sheet 3" why don't you just do that? – Alex M Mar 19 '20 at 21:48
  • @AlexM Is it possible to write a formula that ignores empty cells and cells with string data and only subtracts numerical values? – Mikko Rantalainen Mar 21 '20 at 9:29
  • Yes, it is. Give it a shot and let us know how it goes. – Alex M Mar 23 '20 at 18:11
  • To those that are giving negative votes for this question: care to explain your choice? – Mikko Rantalainen Mar 25 '20 at 6:23
  • 1
    I can't speak for anyone else (and I haven't downvoted; don't see the point) but a total lack of desire to display any effort whatsoever on your own behalf does tend to rub some folks the wrong way. – Alex M Mar 27 '20 at 20:39

The following code compares Sheet1 to Sheet2. If Sheet1 contains either text or blanks, these cells are copied directly to Sheet3. If Sheet1 cells contain numbers, then the code subtracts the value from Sheet2 and stores the difference in Sheet3:

Sub BigDelta()
    Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
    Dim rng As Range, addy As String, v As Variant, cell As Range

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Set s3 = Sheets("Sheet3")
    Set rng = s1.UsedRange

    For Each cell In rng
        addy = cell.Address
        v = cell.Value
        If v = "" Then
        ElseIf IsNumeric(v) Then
            s3.Range(addy) = v - s2.Range(addy)
            s3.Range(addy) = v
        End If
      Next cell
End Sub
  • This is exactly what I want. Is it possible to do this without macros? – Mikko Rantalainen Mar 21 '20 at 9:28

If I understand correctly, the structure of the two tables is the same, only the data in it is different, and you just want to calculate the difference between the data and add it to the third table.

I made a relatively simple sample data, you just need to use the formula simply in the third workbook:"=Sheet2!XX-Sheet1!XX"

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.