Finding difference between data in 2 columns in Excel

Question

My data set looks like this:

col1: 1,4,5,6,24,6

col2: 1,5,4,2,45,6

I want to find:

(col1[0] - col2[0]) and then paste into col3[0]

then

(col1[1] - col2[1]) and then paste into col3[1]

for approx. 4000 entries until there are no more values.

How would I do that using a macro or VB in excel? Thanks in advance.

How would I do that using a macro By simply recording a macro and following the steps that you want :) — Siddharth Rout
I just found a dirty way to do this. Basically: "I played around a bit more. I didn't realise that if I pasted =B1-A1 into the 2nd row, it would magically change the 1 to a 2 for me. So I can paste that formula into the whole column and Excel sorts it out. If you copy any cell with "relative" cell references in them...Excel will adjust those references as you paste them." excelforum.com/excel-new-users/… — Dude

adayzdone adayzdone · Accepted Answer · 2012-05-16T18:28:01

I'm just starting. What is the proper way of doing this Siddharth?

Option Explicit

Sub minus()
Dim lastRow As Integer, i As Integer
lastRow = 7

With ActiveSheet
For i = 1 To lastRow
Range("C" & i).Formula = "=A" & i & "-B" & i
Next i

End With
End Sub

Finding difference between data in 2 columns in Excel

1 Answers