How to insert number of rows in spreadsheet based on a numeric value of an cell?

[GeoDog]

Hello I have a spreadsheet of well log soil/geology depth and thicknesses in feet. Each row has a distinct soil type. Below are three wells 14000000030 & 14000000031 & 14000000032. For instance the 1st well has 3 rows has the 1st soil type from LITH_AGG is Sand & Gravel from 0-55 feet. The 2nd is Clay & Sand from 55-59 feet and the 3rd is Sand & Gravel from 59-86 feet. I need to breakdown each of the depths/thicknesses in 1 foot increments. So the 1st 1st soil type from LITH_AGG is Sand & Gravel from 0-55 feet would end up being 55 rows, 1 for each 1 foot of depth. And so on

WELLID	SEQ_NUM	PRIM_LITH	LITH_MOD	LITH_AGG	TOP	DEPTH	THICKNESS	AQTYPE
14000000030	1.00000000000​	Sand & Gravel	W/Stones	Sand & Gravel	0​	55​	55​	D
14000000030	2.00000000000​	Clay	Sandy	Clay & Sand	55​	59​	4​	D
14000000030	3.00000000000​	Gravel & Sand	Water Bearing	Sand & Gravel	59​	86​	27​	D
14000000031	1.00000000000​	Sand		Sand	0​	8​	8​	D
14000000031	2.00000000000​	Clay		Clay	8​	12​	4​	D
14000000031	3.00000000000​	Gravel		Gravel	12​	69​	57​	D
14000000031	4.00000000000​	Clay & Gravel		Diamicton	69​	82​	13​	D
14000000031	5.00000000000​	Gravel		Gravel	82​	91​	9​	D
14000000031	6.00000000000​	Sand	Coarse	Sand	91​	110​	19​	D
14000000032	1.00000000000​	Topsoil		Organic	0​	1​	1​	D
14000000032	2.00000000000​	Clay		Clay	1​	16​	15​	D
14000000032	3.00000000000​	Clay & Gravel		Diamicton	16​	26​	10​	D
14000000032	4.00000000000​	Sand & Gravel		Sand & Gravel	26​	55​	29​	D
14000000032	5.00000000000​	Clay & Gravel		Diamicton	55​	94​	39​	D
14000000032	6.00000000000​	Sand	Medium	Sand	94​	110​	16​	D

Below is a typical well log similar to the above

WELLID

SEQ_NUM

PRIM_LITH

LITH_MOD

LITH_AGG

TOP

DEPTH

THICKNESS

AQTYPE

CLASS

EFFECT

MAQTYPE

COLOR

14000000033

1.00000000000​

Topsoil

Organic

0​

1​

1​

D

NA

0.00000000000​

NA

14000000033

2.00000000000​

Sand

Sand

1​

20​

19​

D

AQ

0.00000000000​

AQ

14000000033

3.00000000000​

Sand & Gravel

W/Clay

Diamicton

18​

24​

6​

D

AQ

1.00000000000​

MAQ

14000000033

4.00000000000​

Gravel

Coarse

Gravel

24​

30​

6​

D

AQ

0.00000000000​

AQ

This is what I was hoping on getting of 1 row per foot depth

WELLID	SEQ_NUM	PRIM_LITH	LITH_MOD	LITH_AGG	TOP	DEPTH	THICKNESS	TOP1FT	DEPTH1FT	THICKNESS1FT	AQTYPE
14000000033	1.00000000000​	Topsoil		Organic	0​	1​	1​	0​	1​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	1​	2​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	2​	3​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	3​	4​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	4​	5​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	5​	6​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	6​	7​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	7​	8​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	8​	9​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	9​	10​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	10​	11​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	11​	12​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	12​	13​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	13​	14​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	14​	15​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	15​	16​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	16​	17​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	17​	18​	1​	D
14000000033	2.00000000000​	Sand		Sand	1​	20​	19​	18​	19​	1​	D
14000000033	3.00000000000​	Sand & Gravel	W/Clay	Diamicton	20​	24​	4​	19​	20​	1​	D
14000000033	3.00000000000​	Sand & Gravel	W/Clay	Diamicton	20​	24​	4​	20​	21​	1​	D
14000000033	3.00000000000​	Sand & Gravel	W/Clay	Diamicton	20​	24​	4​	21​	22​	1​	D
14000000033	3.00000000000​	Sand & Gravel	W/Clay	Diamicton	20​	24​	4​	22​	23​	1​	D
14000000033	3.00000000000​	Sand & Gravel	W/Clay	Diamicton	20​	24​	4​	23​	24​	1​	D
14000000033	4.00000000000​	Gravel	Coarse	Gravel	24​	30​	6​	24​	25​	1​	D
14000000033	4.00000000000​	Gravel	Coarse	Gravel	24​	30​	6​	25​	26​	1​	D
14000000033	4.00000000000​	Gravel	Coarse	Gravel	24​	30​	6​	25​	26​	1​	D
14000000033	4.00000000000​	Gravel	Coarse	Gravel	24​	30​	6​	27​	28​	1​	D
14000000033	4.00000000000​	Gravel	Coarse	Gravel	24​	30​	6​	28​	29​	1​	D
14000000033	4.00000000000​	Gravel	Coarse	Gravel	24​	30​	6​	29​	30​	1​	D

 

[Dossfm0q]

Try This

Sub Num_Rw_Cell_Val1()
'Quoted from Fluff VBA script Code :https://www.mrexcel.com/board/threads/transpose-every-x-amount-of-columns-to-rows-keeping-cell-of-first-column-on-each-row.1157009/post-5610934
Dim Rng As Range

Dim ArrVal As Variant, CompArr As Variant

Set Rng = Sheets("Sheet1").Range("A1").CurrentRegion
ArrVal = Rng.CurrentRegion.Value2
SumCL8 = Application.Sum(Rng.Columns(8))
ReDim CopmArr(1 To SumCL8 + 10, 1 To Rng.Columns.Count)
For r = 2 To UBound(ArrVal)
    For c = 8 To 8
        If IsNumeric(ArrVal(r, c)) = True Then
            NumRw = ArrVal(r, c)
            For rw = 1 To NumRw
            nr = nr + 1
                For cl = 1 To 9
                CopmArr(nr, cl) = ArrVal(r, c + cl - 8)
                Next
            Next
        End If
    Next
Next

Range("A25").Resize(nr, Rng.Columns.Count) = CopmArr
End Sub

Some Rows of

Book1
	A	B	C	D	E	F	G	H	I
1	WELLID	SEQ_NUM	PRIM_LITH	LITH_MOD	LITH_AGG	TOP	DEPTH	THICKNESS	AQTYPE
2	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
3	14000000030	2	Clay	Sandy	Clay & Sand	55	59	4	D
4	14000000030	3	Gravel & Sand	Water Bearing	Sand & Gravel	59	86	27	D
5	14000000031	1	Sand		Sand	0	8	8	D
6	14000000031	2	Clay		Clay	8	12	4	D
7	14000000031	3	Gravel		Gravel	12	69	57	D
8	14000000031	4	Clay & Gravel		Diamicton	69	82	13	D
9	14000000031	5	Gravel		Gravel	82	91	9	D
10	14000000031	6	Sand	Coarse	Sand	91	110	19	D
11	14000000032	1	Topsoil		Organic	0	1	1	D
12	14000000032	2	Clay		Clay	1	16	15	D
13	14000000032	3	Clay & Gravel		Diamicton	16	26	10	D
14	14000000032	4	Sand & Gravel		Sand & Gravel	26	55	29	D
15	14000000032	5	Clay & Gravel		Diamicton	55	94	39	D
16	14000000032	6	Sand	Medium	Sand	94	110	16	D
17
18
19
20
21
22
23
24
25	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
26	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
27	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
28	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
29	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
30	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
31	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
32	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
33	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
34	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
35	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
36	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
37	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
38	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
39	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
40	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
41	14000000030	1	Sand & Gravel	W/Stones	Sand & Gravel	0	55	55	D
Sheet1

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm hoping that you have some typos in your sample data and expected results.
A few examples:

1. Sand goes from 1 to 20 in the data but the deepest sand in your results is 18 to 19
2. Sand & Gravel starts at 18 in the data but starts at 19 in the results
3. Sand & Gravel has thickness of 6 in the data but 4 in the results.

This would be my interpretation of what you want.

Sub LayerByFoot()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, r As Long, c As Long, ubb2 As Long
  
  a = Range("A2", Range("I" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 12)
  ubb2 = UBound(b, 2)
  For i = 1 To UBound(a)
    r = 0
    For r = a(i, 6) To a(i, 7) - 1
      k = k + 1
      For c = 1 To 8
        b(k, c) = a(i, c)
      Next c
      b(k, 9) = r
      b(k, 10) = r + 1
      b(k, 11) = 1
      b(k, 12) = a(i, 9)
    Next r
  Next i
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, ubb2).Value = b
End Sub

Here is my sample data with results below.

GeoDog.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	WELLID	SEQ_NUM	PRIM_LITH	LITH_MOD	LITH_AGG	TOP	DEPTH	THICKNESS	AQTYPE	CLASS	EFFECT	MAQTYPE	COLOR
2	14000000033	1	Topsoil		Organic	0	1	1	D	NA	0	NA
3	14000000033	2	Sand		Sand	1	20	19	D	AQ	0	AQ
4	14000000033	3	Sand & Gravel	W/Clay	Diamicton	18	24	6	D	AQ	1	MAQ
5	14000000033	4	Gravel	Coarse	Gravel	24	30	6	D	AQ	0	AQ
6
7
8	14000000033	1	Topsoil		Organic	0	1	1	0	1	1	D
9	14000000033	2	Sand		Sand	1	20	19	1	2	1	D
10	14000000033	2	Sand		Sand	1	20	19	2	3	1	D
11	14000000033	2	Sand		Sand	1	20	19	3	4	1	D
12	14000000033	2	Sand		Sand	1	20	19	4	5	1	D
13	14000000033	2	Sand		Sand	1	20	19	5	6	1	D
14	14000000033	2	Sand		Sand	1	20	19	6	7	1	D
15	14000000033	2	Sand		Sand	1	20	19	7	8	1	D
16	14000000033	2	Sand		Sand	1	20	19	8	9	1	D
17	14000000033	2	Sand		Sand	1	20	19	9	10	1	D
18	14000000033	2	Sand		Sand	1	20	19	10	11	1	D
19	14000000033	2	Sand		Sand	1	20	19	11	12	1	D
20	14000000033	2	Sand		Sand	1	20	19	12	13	1	D
21	14000000033	2	Sand		Sand	1	20	19	13	14	1	D
22	14000000033	2	Sand		Sand	1	20	19	14	15	1	D
23	14000000033	2	Sand		Sand	1	20	19	15	16	1	D
24	14000000033	2	Sand		Sand	1	20	19	16	17	1	D
25	14000000033	2	Sand		Sand	1	20	19	17	18	1	D
26	14000000033	2	Sand		Sand	1	20	19	18	19	1	D
27	14000000033	2	Sand		Sand	1	20	19	19	20	1	D
28	14000000033	3	Sand & Gravel	W/Clay	Diamicton	18	24	6	18	19	1	D
29	14000000033	3	Sand & Gravel	W/Clay	Diamicton	18	24	6	19	20	1	D
30	14000000033	3	Sand & Gravel	W/Clay	Diamicton	18	24	6	20	21	1	D
31	14000000033	3	Sand & Gravel	W/Clay	Diamicton	18	24	6	21	22	1	D
32	14000000033	3	Sand & Gravel	W/Clay	Diamicton	18	24	6	22	23	1	D
33	14000000033	3	Sand & Gravel	W/Clay	Diamicton	18	24	6	23	24	1	D
34	14000000033	4	Gravel	Coarse	Gravel	24	30	6	24	25	1	D
35	14000000033	4	Gravel	Coarse	Gravel	24	30	6	25	26	1	D
36	14000000033	4	Gravel	Coarse	Gravel	24	30	6	26	27	1	D
37	14000000033	4	Gravel	Coarse	Gravel	24	30	6	27	28	1	D
38	14000000033	4	Gravel	Coarse	Gravel	24	30	6	28	29	1	D
39	14000000033	4	Gravel	Coarse	Gravel	24	30	6	29	30	1	D
Sheet1

 

[GeoDog]

Yes you were right I did have some typos, sorry. Wow this worked the 1st time I tried it. Thank you so much. One thing is that it overwrote the 2 of the existing columns that have important data. Would it be possible to have it write to 2 new fields at the end of the table in columns N and O, like TOP1FTI DEPTH1FTI? Would it be possible to have it write the data to a new sheet?
Again, thank you so much.

 

[Peter_SSs]

Would it be possible to have it write the data to a new sheet?

Like this?

Sub LayerByFoot_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, r As Long, c As Long, ubb2 As Long
  
  a = Range("A2", Range("I" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 12)
  ubb2 = UBound(b, 2)
  For i = 1 To UBound(a)
    r = 0
    For r = a(i, 6) To a(i, 7) - 1
      k = k + 1
      For c = 1 To 8
        b(k, c) = a(i, c)
      Next c
      b(k, 9) = r
      b(k, 10) = r + 1
      b(k, 11) = 1
      b(k, 12) = a(i, 9)
    Next r
  Next i
  With Sheets.Add(After:=ActiveSheet).Range("A2").Resize(k, ubb2)
    .Value = b
    .Rows(0).Value = Array("WELLID", "SEQ_NUM", "PRIM_LITH", "LITH_MOD", "LITH_AGG", "TOP", "DEPTH", "THICKNESS", "TOP1FT", "DEPTH1FT", "THICKNESS1FT", "AQTYPE")
    .EntireColumn.AutoFit
  End With
End Sub