# Subtract previous cells in a row and report in new sheet

#### ekalavya

##### New Member
I have a data file that looks like this:

 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 Col11 Col12 Col13 Col14 1 0 B 168850000 A 176250000 B 189350000 A 220150000 B 272571316 2 0 A 226950000 B 272571316 3 0 A 28250000 B 38650000 A 242950000 B 261450000 A 265550000 B 272571316 4 0 A 39650000 B 228150000 A 272571316 5 0 A 39650000 B 239650000 A 272571316 6 0 A 19250000 B 45750000 A 242950000 B 249950000 A 272571316

<tbody>
</tbody>

For each sample (Col1), I want to subtract the values spanning each letter. For example, resulting values for sample 1 would be: Col4-Col2 for B, Col6-Col4 for A, Col8-Col6 for B, Col10-Col8 for A, and so on until the last column. The number of columns varies by the sample, with minimum of 4 columns to maximum of about 100. There are about 2500 rows.

At the end, I would prefer to have the result in a new sheet as follows:

 1B 168850000 1A 7400000 1B 13100000 1A 30800000 1B 52421316 2A 226950000 2B 45621316 3A 28250000 3B 10400000 and so on

<tbody>
</tbody>

Any help would be greatly appreciated. Thank you.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Osvaldo Palmeiro

##### Well-known Member
Hi.
Would you try a solution via macro?

Code:
``````Sub Test()
Dim LR As Long, LC As Long, k As Long, x As Long, m As Long
Dim wsO As Worksheet, wsD As Worksheet
Set wsO = Sheets("Sheet1"): Set wsD = Sheets("Sheet2")
wsD.[A:B] = ""
LR = wsO.Cells(Rows.Count, 1).End(3).Row
For k = 2 To LR
LC = wsO.Cells(k, Columns.Count).End(1).Column
For x = 3 To LC - 1 Step 2
With wsD
.Cells(m + 2,  1) = wsO.Cells(k, 1) & wsO.Cells(k, x)
.Cells(m + 2, 2) = wsO.Cells(k, x + 1) - wsO.Cells(k, x - 1)
m = m + 1
End With
Next x
Next k
End Sub``````

Last edited:

#### ekalavya

##### New Member
Hi.
Would you try a solution via macro?
Hi Osvaldo,

The macro you provided works great. And it is very fast as well (maybe 1-2 seconds total).

Thank you very much for the time you put in to help me! Much appreciated.

Replies
3
Views
555
Replies
1
Views
3K
Replies
9
Views
223
Replies
1
Views
247
Replies
13
Views
742

1,191,685
Messages
5,988,002
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back