Subtract previous cells in a row and report in new sheet

[ekalavya]

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.

 

[Osvaldo Palmeiro]

Hi.
Would you try a solution via macro?

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

 

[ekalavya]

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.