ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How to line up two dates? (https://www.excelbanter.com/excel-discussion-misc-queries/259511-how-line-up-two-dates.html)

Tom
How to line up two dates?
 
Before I can carry out other task, I have to line up the row where Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down as
shown below. How would you write the codes to do that? Thanks.

Code Date 1 Date 2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108
20090109
20090112
20090113

Code Date 1 Date 2
20090101
20090102
20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113


TIA
Tom



Tom
How to line up two dates?
 
Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down as
shown below. How would you write the codes to do that? Thanks.

Code Date 1 Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1 Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom



Stefi
How to line up two dates?
 
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



Tom ezt *rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down as
shown below. How would you write the codes to do that? Thanks.

Code Date 1 Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1 Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.

Tom
How to line up two dates?
 
I'm looking for an automated process, not visual, whereby the first date on
column 2 lines up with its counterpart on column G which can be several rows
down. This program needs to be able to read the content of A2, and determine
its row position, finds its counterpart and its row position. Now, knowing
the row difference, then move A2:G2 down that number of rows.

"Stefi" wrote in message
...
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



"Tom" ezt rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down as
shown below. How would you write the codes to do that? Thanks.

Code Date 1
Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1
Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.

Stefi
How to line up two dates?
 
You should have specified exactly your request! This one line macro does the
job:

Sub lineup()
Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert
Shift:=xlDown
End Sub

--
Regards!
Stefi



Tom ezt *rta:

I'm looking for an automated process, not visual, whereby the first date on
column 2 lines up with its counterpart on column G which can be several rows
down. This program needs to be able to read the content of A2, and determine
its row position, finds its counterpart and its row position. Now, knowing
the row difference, then move A2:G2 down that number of rows.

"Stefi" wrote in message
...
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



"Tom" ezt *rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down as
shown below. How would you write the codes to do that? Thanks.

Code Date 1
Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1
Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.



.

Tom
How to line up two dates?
 
Works great. Thank you Stefi.

"Stefi" wrote in message
...
You should have specified exactly your request! This one line macro does
the
job:

Sub lineup()
Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert
Shift:=xlDown
End Sub

--
Regards!
Stefi



"Tom" ezt rta:

I'm looking for an automated process, not visual, whereby the first date
on
column 2 lines up with its counterpart on column G which can be several
rows
down. This program needs to be able to read the content of A2, and
determine
its row position, finds its counterpart and its row position. Now,
knowing
the row difference, then move A2:G2 down that number of rows.

"Stefi" wrote in message
...
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



"Tom" ezt rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where
Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down
as
shown below. How would you write the codes to do that? Thanks.

Code Date 1
Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1
Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.



.

Tom
How to line up two dates?
 
Just found a problem. If the two dates are already aligned, the program also
shift it down two rows which it shouldn't have done. May be it should check
for alignment first.

"Stefi" wrote in message
...
You should have specified exactly your request! This one line macro does
the
job:

Sub lineup()
Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert
Shift:=xlDown
End Sub

--
Regards!
Stefi



"Tom" ezt rta:

I'm looking for an automated process, not visual, whereby the first date
on
column 2 lines up with its counterpart on column G which can be several
rows
down. This program needs to be able to read the content of A2, and
determine
its row position, finds its counterpart and its row position. Now,
knowing
the row difference, then move A2:G2 down that number of rows.

"Stefi" wrote in message
...
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



"Tom" ezt rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where
Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down
as
shown below. How would you write the codes to do that? Thanks.

Code Date 1
Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1
Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.



.

Stefi
How to line up two dates?
 
Sub lineup()
rowshift = Columns("H").Find(Range("B2")).Row - 1
If rowshift 1 Then Range("A2:G" & rowshift).Insert Shift:=xlDown
End Sub

Clicking the YES button will be appreciated.

--
Regards!
Stefi



Tom ezt *rta:

Just found a problem. If the two dates are already aligned, the program also
shift it down two rows which it shouldn't have done. May be it should check
for alignment first.

"Stefi" wrote in message
...
You should have specified exactly your request! This one line macro does
the
job:

Sub lineup()
Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert
Shift:=xlDown
End Sub

--
Regards!
Stefi



"Tom" ezt *rta:

I'm looking for an automated process, not visual, whereby the first date
on
column 2 lines up with its counterpart on column G which can be several
rows
down. This program needs to be able to read the content of A2, and
determine
its row position, finds its counterpart and its row position. Now,
knowing
the row difference, then move A2:G2 down that number of rows.

"Stefi" wrote in message
...
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



"Tom" ezt *rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where
Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down
as
shown below. How would you write the codes to do that? Thanks.

Code Date 1
Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1
Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.



.



.

Tom
How to line up two dates?
 
Now, that works perfectly. Thanks again Stefi.
I'm accessing this newsgroup using Outlook Express and I can't see any Yes
button in it to click. Which website are you posting this Stefi?

Regards,
Tom

"Stefi" wrote in message
...
Sub lineup()
rowshift = Columns("H").Find(Range("B2")).Row - 1
If rowshift 1 Then Range("A2:G" & rowshift).Insert Shift:=xlDown
End Sub

Clicking the YES button will be appreciated.

--
Regards!
Stefi



"Tom" ezt rta:

Just found a problem. If the two dates are already aligned, the program
also
shift it down two rows which it shouldn't have done. May be it should
check
for alignment first.

"Stefi" wrote in message
...
You should have specified exactly your request! This one line macro
does
the
job:

Sub lineup()
Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert
Shift:=xlDown
End Sub

--
Regards!
Stefi



"Tom" ezt rta:

I'm looking for an automated process, not visual, whereby the first
date
on
column 2 lines up with its counterpart on column G which can be
several
rows
down. This program needs to be able to read the content of A2, and
determine
its row position, finds its counterpart and its row position. Now,
knowing
the row difference, then move A2:G2 down that number of rows.

"Stefi" wrote in message
...
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



"Tom" ezt rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where
Date 1
starts with Date 2 for all other stocks. It is not always 3 rows
down
as
shown below. How would you write the codes to do that? Thanks.

Code Date 1
Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1
Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.



.



.

Stefi
How to line up two dates?
 
Never mind, Tom, "Yes" is only in MS Office online site.

--
Regards!
Stefi



Tom ezt *rta:

Now, that works perfectly. Thanks again Stefi.
I'm accessing this newsgroup using Outlook Express and I can't see any Yes
button in it to click. Which website are you posting this Stefi?

Regards,
Tom

"Stefi" wrote in message
...
Sub lineup()
rowshift = Columns("H").Find(Range("B2")).Row - 1
If rowshift 1 Then Range("A2:G" & rowshift).Insert Shift:=xlDown
End Sub

Clicking the YES button will be appreciated.

--
Regards!
Stefi



"Tom" ezt *rta:

Just found a problem. If the two dates are already aligned, the program
also
shift it down two rows which it shouldn't have done. May be it should
check
for alignment first.

"Stefi" wrote in message
...
You should have specified exactly your request! This one line macro
does
the
job:

Sub lineup()
Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert
Shift:=xlDown
End Sub

--
Regards!
Stefi



"Tom" ezt *rta:

I'm looking for an automated process, not visual, whereby the first
date
on
column 2 lines up with its counterpart on column G which can be
several
rows
down. This program needs to be able to read the content of A2, and
determine
its row position, finds its counterpart and its row position. Now,
knowing
the row difference, then move A2:G2 down that number of rows.

"Stefi" wrote in message
...
Select range A2:G4, then InsertCellsShift cells down!


--
Regards!
Stefi



"Tom" ezt *rta:

Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where
Date 1
starts with Date 2 for all other stocks. It is not always 3 rows
down
as
shown below. How would you write the codes to do that? Thanks.

Code Date 1
Date
2
ABW 20090106 5.85 5.85 5.85 5.85 242 20090101
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108

20090109

20090112

20090113

Code Date 1
Date
2

20090101

20090102

20090105
ABW 20090106 5.85 5.85 5.85 5.85 242 20090106
ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107
ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108
ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109
ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112
ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113

TIA
Tom


.



.



.



.


All times are GMT +1. The time now is 05:48 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com