2 Lists but different time frames.


G

gaga.kaplan

Hi All,
This is a snipet of my spreadsheet, this goes one untill 2007...:

A B C D E
1 GDP GDP
2 Jan-1981 5,307.5 1981q1 5,307.5
3 Feb-1981 5,307.5 1981q2 5,266.1
4 Mar-1981 5,307.5 1981q3 5,329.8
5 Apr-1981 5,266.1 1981q4 5,263.4
6 May-1981 5,266.1 1982q1 5,177.1
7 Jun-1981 5,266.1 1982q2 5,204.9
8 Jul-1981 5,329.8 1982q3 5,185.2
9 Aug-1981 5,329.8 1982q4 5,189.8
10 Sep-1981 5,329.8 1983q1 5,253.8
11 Oct-1981 5,263.4 1983q2 5,372.3
12 Nov-1981 5,263.4 1983q3 5,478.4
13 Dec-1981 5,263.4 1983q4 5,590.5
14 Jan-1982 5,177.1 1984q1 5,699.8
15 Feb-1982 5,177.1 1984q2 5,797.9
16 Mar-1982 5,177.1 1984q3 5,854.3
17 Apr-1982 1984q4 5,902.4
18 May-1982 1985q1 5,956.9
19 Jun-1982 1985q2 6,007.8
20 Jul-1982 1985q3 6,101.7
21 Aug-1982 1985q4 6,148.6
22 Sep-1982 1986q1 6,207.4

What I want to do is to continue copying values automatically from the
E column into the B column in the appropriate time frame.
Q1 = Jan, Feb, Mar
Q2 = Apr, May, Jun
Q3 = Jul, Aug, Sep
Q4 = Oct, Nov, Dec

What code would I use?
 
Ad

Advertisements

G

gaga.kaplan

Hi All,
This is a snipet of my spreadsheet, this goes one untill 2007...:

A B C D E
1 GDP GDP
2 Jan-1981 5,307.5 1981q1 5,307.5
3 Feb-1981 5,307.5 1981q2 5,266.1
4 Mar-1981 5,307.5 1981q3 5,329.8
5 Apr-1981 5,266.1 1981q4 5,263.4
6 May-1981 5,266.1 1982q1 5,177.1
7 Jun-1981 5,266.1 1982q2 5,204.9
8 Jul-1981 5,329.8 1982q3 5,185.2
9 Aug-1981 5,329.8 1982q4 5,189.8
10 Sep-1981 5,329.8 1983q1 5,253.8
11 Oct-1981 5,263.4 1983q2 5,372.3
12 Nov-1981 5,263.4 1983q3 5,478.4
13 Dec-1981 5,263.4 1983q4 5,590.5
14 Jan-1982 5,177.1 1984q1 5,699.8
15 Feb-1982 5,177.1 1984q2 5,797.9
16 Mar-1982 5,177.1 1984q3 5,854.3
17 Apr-1982 1984q4 5,902.4
18 May-1982 1985q1 5,956.9
19 Jun-1982 1985q2 6,007.8
20 Jul-1982 1985q3 6,101.7
21 Aug-1982 1985q4 6,148.6
22 Sep-1982 1986q1 6,207.4

What I want to do is to continue copying values automatically from the
E column into the B column in the appropriate time frame.
Q1 = Jan, Feb, Mar
Q2 = Apr, May, Jun
Q3 = Jul, Aug, Sep
Q4 = Oct, Nov, Dec

What code would I use?

I didnt' come out very well,
Column A refers to the monthly change
Column B is the same as Column E
Column C is blank
Column D are the quarters for each year.
 
G

Guest

This will work. I had a slight mistake with your previous posting that the
months were off by one month. this code I fully tested. I converted the
month year to a Quarter and then did a lookup in column D to find the correct
quarter.



Sub combineGDP()

RowCount = 2
Do While Range("A" & RowCount) <> ""
If Range("B" & RowCount) = "" Then
MyDate = Range("A" & RowCount)
MyYear = Year(MyDate)
MyMonth = Month(MyDate)
Quarter = Int((MyMonth - 1) / 3) + 1
SearchDate = MyYear & "q" & Quarter
Set c = Columns("D:D").Find(what:=SearchDate, _
LookIn:=xlValues)
If Not c Is Nothing Then
Range("B" & RowCount) = c.Offset(0, 1)
End If
End If
RowCount = RowCount + 1
Loop

End Sub
 
Ad

Advertisements

G

gaga.kaplan

This will work. I had a slight mistake with your previous posting that the
months were off by one month. this code I fully tested. I converted the
month year to a Quarter and then did a lookup in column D to find the correct
quarter.

Sub combineGDP()

RowCount = 2
Do While Range("A" & RowCount) <> ""
If Range("B" & RowCount) = "" Then
MyDate = Range("A" & RowCount)
MyYear = Year(MyDate)
MyMonth = Month(MyDate)
Quarter = Int((MyMonth - 1) / 3) + 1
SearchDate = MyYear & "q" & Quarter
Set c = Columns("D:D").Find(what:=SearchDate, _
LookIn:=xlValues)
If Not c Is Nothing Then
Range("B" & RowCount) = c.Offset(0, 1)
End If
End If
RowCount = RowCount + 1
Loop

End Sub






- Show quoted text -

Thanks, I really appreciate it...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top