Simple Copy

rcsberg

New Member
Joined
Oct 9, 2019
Messages
2
How can I extract the year "1851" from "AL011851" and copy that year for 14 rows. This process repeats with the variables being the year and the number of rows for that particular year. The number of rows to copy is given in a certain cell in column F.
There are 52,000 rows in total.

Example 1:
E
xtract the year "1851" from "AL011851" (in column 1) and copy the 4-digit year to another column for 14 rows.

Example 2:
Extract the year "1851" from "AL021851"
(in column 1) and copy the 4-digit year to another column
for 1 row.


Example 3:
Extract the year "1980" from "AL021980"
(in column 1) and copy the 4-digit year to another column
for 49 rows.

Etc...for 52,000 rows.

Thank you in advance!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Hi @★ rcsberg, welcome to the forum!

Any doubts:
- The year is always the 4 digits to the right of the text?
- How to know how many rows the year should be copied?
- In which other column and in which rows should the year be pasted?
- You could give an example of how your data looks. Just copy the range of your cells and paste here. Or upload an image to dropbox.
 

rcsberg

New Member
Joined
Oct 9, 2019
Messages
2
Hi Dante,

Thanks for the reply.

- The year is always the 4 digits to the right of the text?
1. Column A has the storm name number "AL011851" with year to the right, followed by successive updates for the storm. Each update "18510625" is written with the year to the right.
- How to know how many rows the year should be copied?
1. Column E has the number of rows for each storm.

Column A has the storm name number "AL011851" with year to the right, followed by successive updates for the storm. Each update "18510625" is written with the year to the right and month and day to the left.
Column B only has the storm name number "AL011851"
Column C has the actual name of the storm "UNNAMED"
Column D is a duplicate of Column C
Column E has the number of rows representing the updates for each storm "14"


I hope this table is formatted correctly for you to read. Hope this helps.


A B C D E
AL011851 AL011851 UNNAMED UNNAMED 14
18510625 0 0
18510625 600 600
18510625 1200 1200
18510625 1800 1800
18510625 2100 2100
18510626 0 0
18510626 600 600
18510626 1200 1200
18510626 1800 1800
18510627 0 0
18510627 600 600
18510627 1200 1200
18510627 1800 1800
18510628 0 0
AL021851 AL021851 UNNAMED UNNAMED 1
18510705 1200 1200
AL031851 AL031851 UNNAMED UNNAMED 1
18510710 1200 1200
AL041851 AL041851 UNNAMED UNNAMED 49
18510816 0 0
18510816 600 600
18510816 1200 1200
18510816 1800 1800
18510817 0 0
18510817 600 600
18510817 1200 1200
18510817 1800 1800
18510818 0 0
18510818 600 600
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Run this macro

Code:
Sub Simple_copy()
  Dim c As Range
  For Each c In Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    c.Offset(1).Resize(c.Value).Value = Right(Cells(c.Row, "A"), 4)
  Next
End Sub
 

Forum statistics

Threads
1,078,273
Messages
5,339,190
Members
399,288
Latest member
ossa

Some videos you may like

This Week's Hot Topics

Top