|
VLookup+ Monthly Data
Hi I am trying to save some time Cut and Paste.
I have values as follows for each month Col A Col B Jan 5.15 Feb 3.20 - Dec 6.75 I then have a 100 years of daily data Col X Col Y 1 Jan 1900 2 Jan 1900 3 Jan 1900 - 31 Dec 1999 I want to apply the value for January (5.15) to Col Y for 1-31st January 00-99, then same for Feb, march etc. I need to do this fairly frequently to copy into another program. Probably a simple answer, I have used VLookup before but cant get it to work for this. Many thanks Chris |
VLookup+ Monthly Data
I will assume:
1) In Col A you have text not dates 2) in Col X you have real dates In AA1 use the formula =CHOOSE(MONTH(X1),"Jan","Feb", "Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") Copy it down the column; giving text like: Jan, Feb .. In X1 use the formula =VLOOKUP(AA1,$A$1:$B$12,2,FALSE) Copy it down the column I will leave it to use to combine the AA1 formula into the X1 formula if so desired best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chris26" wrote in message ... Hi I am trying to save some time Cut and Paste. I have values as follows for each month Col A Col B Jan 5.15 Feb 3.20 - Dec 6.75 I then have a 100 years of daily data Col X Col Y 1 Jan 1900 2 Jan 1900 3 Jan 1900 - 31 Dec 1999 I want to apply the value for January (5.15) to Col Y for 1-31st January 00-99, then same for Feb, march etc. I need to do this fairly frequently to copy into another program. Probably a simple answer, I have used VLookup before but cant get it to work for this. Many thanks Chris |
VLookup+ Monthly Data
That worked, Thank you
"Bernard Liengme" wrote: I will assume: 1) In Col A you have text not dates 2) in Col X you have real dates In AA1 use the formula =CHOOSE(MONTH(X1),"Jan","Feb", "Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") Copy it down the column; giving text like: Jan, Feb .. In X1 use the formula =VLOOKUP(AA1,$A$1:$B$12,2,FALSE) Copy it down the column I will leave it to use to combine the AA1 formula into the X1 formula if so desired best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chris26" wrote in message ... Hi I am trying to save some time Cut and Paste. I have values as follows for each month Col A Col B Jan 5.15 Feb 3.20 - Dec 6.75 I then have a 100 years of daily data Col X Col Y 1 Jan 1900 2 Jan 1900 3 Jan 1900 - 31 Dec 1999 I want to apply the value for January (5.15) to Col Y for 1-31st January 00-99, then same for Feb, march etc. I need to do this fairly frequently to copy into another program. Probably a simple answer, I have used VLookup before but cant get it to work for this. Many thanks Chris |
VLookup+ Monthly Data
=CHOOSE(MONTH(X1),"Jan","Feb",
"Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") shorter version: =TEXT(X1,"mmm") "Bernard Liengme" wrote: I will assume: 1) In Col A you have text not dates 2) in Col X you have real dates In AA1 use the formula =CHOOSE(MONTH(X1),"Jan","Feb", "Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") Copy it down the column; giving text like: Jan, Feb .. In X1 use the formula =VLOOKUP(AA1,$A$1:$B$12,2,FALSE) Copy it down the column I will leave it to use to combine the AA1 formula into the X1 formula if so desired best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chris26" wrote in message ... Hi I am trying to save some time Cut and Paste. I have values as follows for each month Col A Col B Jan 5.15 Feb 3.20 - Dec 6.75 I then have a 100 years of daily data Col X Col Y 1 Jan 1900 2 Jan 1900 3 Jan 1900 - 31 Dec 1999 I want to apply the value for January (5.15) to Col Y for 1-31st January 00-99, then same for Feb, march etc. I need to do this fairly frequently to copy into another program. Probably a simple answer, I have used VLookup before but cant get it to work for this. Many thanks Chris |
VLookup+ Monthly Data
Good point! I must have more coffee!
best wishes -- Bernard "Teethless mama" wrote in message ... =CHOOSE(MONTH(X1),"Jan","Feb", "Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") shorter version: =TEXT(X1,"mmm") "Bernard Liengme" wrote: I will assume: 1) In Col A you have text not dates 2) in Col X you have real dates In AA1 use the formula =CHOOSE(MONTH(X1),"Jan","Feb", "Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") Copy it down the column; giving text like: Jan, Feb .. In X1 use the formula =VLOOKUP(AA1,$A$1:$B$12,2,FALSE) Copy it down the column I will leave it to use to combine the AA1 formula into the X1 formula if so desired best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chris26" wrote in message ... Hi I am trying to save some time Cut and Paste. I have values as follows for each month Col A Col B Jan 5.15 Feb 3.20 - Dec 6.75 I then have a 100 years of daily data Col X Col Y 1 Jan 1900 2 Jan 1900 3 Jan 1900 - 31 Dec 1999 I want to apply the value for January (5.15) to Col Y for 1-31st January 00-99, then same for Feb, march etc. I need to do this fairly frequently to copy into another program. Probably a simple answer, I have used VLookup before but cant get it to work for this. Many thanks Chris |
VLookup+ Monthly Data
Now I recall why I used the longer method: it allows for non-standard text
abbreviations in the OP's data -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Teethless mama" wrote in message ... =CHOOSE(MONTH(X1),"Jan","Feb", "Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") shorter version: =TEXT(X1,"mmm") "Bernard Liengme" wrote: I will assume: 1) In Col A you have text not dates 2) in Col X you have real dates In AA1 use the formula =CHOOSE(MONTH(X1),"Jan","Feb", "Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","N ov","Dec") Copy it down the column; giving text like: Jan, Feb .. In X1 use the formula =VLOOKUP(AA1,$A$1:$B$12,2,FALSE) Copy it down the column I will leave it to use to combine the AA1 formula into the X1 formula if so desired best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chris26" wrote in message ... Hi I am trying to save some time Cut and Paste. I have values as follows for each month Col A Col B Jan 5.15 Feb 3.20 - Dec 6.75 I then have a 100 years of daily data Col X Col Y 1 Jan 1900 2 Jan 1900 3 Jan 1900 - 31 Dec 1999 I want to apply the value for January (5.15) to Col Y for 1-31st January 00-99, then same for Feb, march etc. I need to do this fairly frequently to copy into another program. Probably a simple answer, I have used VLookup before but cant get it to work for this. Many thanks Chris |
| All times are GMT +1. The time now is 09:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com