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,975
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,975
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,520
Messages
5,340,922
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top