| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting daily data into weekly, monthly and yearly data | Excel Discussion (Misc queries) | |||
| How do I get monthly data in the data area to remain in order | Charts and Charting in Excel | |||
| VLOOKUP specific monthly accounting data by selecting drop down li | Excel Worksheet Functions | |||
| How do I set up monthly random work schedule for 60 hours monthly | Excel Discussion (Misc queries) | |||
| Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) | |||
Linear Mode
