Splitting big value into smaller values

[mythcif]

Dear All

I have some spreadsheets containing substantial rows of data, basically 3 columns as this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sample[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Difference (End-Start)[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1500
[/TD]
[TD]1600
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1200
[/TD]
[TD]1260
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]960
[/TD]
[TD]1020
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]900[/TD]
[TD]920[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

And what i want to obtain is different rows of data such as every row has the same difference (20 in this case, without changing the first column), like the following (never mind the colors, they are only to make my point clear):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sample[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Difference (End-Start)[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1500
[/TD]
[TD]1520
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1520
[/TD]
[TD]1540
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1540
[/TD]
[TD]1560
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1560
[/TD]
[TD]1580
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1580
[/TD]
[TD]1600
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1200
[/TD]
[TD]1220
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1220
[/TD]
[TD]1240
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1220
[/TD]
[TD]1260
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]960
[/TD]
[TD]980
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]980
[/TD]
[TD]1000
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]1000
[/TD]
[TD]1020
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]900[/TD]
[TD]920[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

We can also think about splitting only one certain number into 20's, like in the case of samples B and C above, because I have already split the data into separate sheets based on their "difference" value, using the help in this link:
http://www.mrexcel.com/forum/excel-questions/396069-copy-new-worksheets.html

Do you think it is doable?

Many thanks in advance,

Best,

m.

 

[VBA Geek]

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1	Sample	Start	End	Difference (End-Start)			Sample	Start	End	Difference(End-Start)
2	A	1500	1600	100			A	1500	1520	20
3	B	1200	1260	60			A	1520	1540	20
4	C	960	1020	60			A	1540	1560	20
5	D	900	920	20			A	1560	1580	20
6							A	1580	1600	20
7							B	1200	1220	20
8							B	1220	1240	20
9							B	1240	1260	20
10							C	960	980	20
11							C	980	1000	20
12							C	1000	1020	20
13							D	900	920	20
14
15
16
Sheet3

Sub SSSSS()
Dim c00      As Variant
Dim fData()  As Variant
Dim UBfDa    As Integer
Dim iRow     As Integer
Dim iCount   As Integer
Dim iDiff    As Integer
Dim iC       As Integer
Const iScale As Integer = 20


iRow = Range("D" & Rows.Count).End(xlUp).Row


' original array
c00 = Range("A2:D" & iRow)


' ubound of new array
UBfDa = Evaluate("SUM((D2:D" & iRow & ")/" & iScale & ")")
ReDim fData(1 To UBfDa, 1 To 4)


UBfDa = 1


For iCount = 1 To UBound(c00, 1)
    iDiff = c00(iCount, 4) / iScale
        For iC = 1 To iDiff
            fData(UBfDa, 1) = c00(iCount, 1)
            fData(UBfDa, 2) = c00(iCount, 2) + ((iC - 1) * iScale)
            fData(UBfDa, 3) = fData(UBfDa, 2) + iScale
            fData(UBfDa, 4) = iScale
            UBfDa = UBfDa + 1
        Next
Next
Range("G1:J1") = Array("Sample", "Start", "End", "Difference(End-Start)")
Range("G2").Resize(UBound(fData, 1), UBound(fData, 2)) = fData
End Sub

 

[mythcif]

Dear VBA Geek,
It works great. Thanks a lot for the quick reply.
Best,
m.