Solved

# Converting mothly data to daily data

Posted on 2011-03-14
289 Views
This is a slightly different question to the one answered by Teylyn a little while ago.

In column A are daily dates in format yyyymmdd.  In column L are monthly dates in format Jan-90.  In columns N-R are numbers (1 or 0) relating to each monthly date.  I want to create a column of numbers (in say, columns C-G) for each day that match the respective number in columns N-R for the given month.  So for example, all of the daily numbers in column C for the month of January 1990 in column A would be the same as the number for Jan-90 in column N.

I have attached a spreadsheet as an example. Sample.xlsx
0
Question by:pwflexner
• 4

LVL 50

Expert Comment

ID: 35133436
Hello,

In C8, copy across and down:

=INDEX(\$N\$8:\$R\$14,MATCH(DATE(LEFT(\$A8,4),MID(\$A8,5,2),1),\$L\$8:\$L\$14,0),MATCH(C\$5,\$N\$5:\$R\$5,0))

Adjust the bold parts to reflect the extent of your month lookup table.

cheers, teylyn
0

LVL 50

Accepted Solution

teylyn earned 125 total points
ID: 35133469
Or with Vlookup

=VLOOKUP(DATE(LEFT(\$A8,4),MID(\$A8,5,2),1),\$L\$5:\$R\$15,MATCH(C\$5,\$L\$5:\$R\$5,0),FALSE)

cheers,
0

LVL 9

Expert Comment

ID: 35133491
In B8 write this formula - "=DATE(LEFT(A8,4),MID(A8,5,2),1)"

In C8 write this - "=VLOOKUP(\$B8,\$L\$8:\$R\$14,3,FALSE)"

Copy paste the two formulas (all the way down in B column).

When you copy it to D8, change the 3 to 4 ... and so on.

Ss
0

LVL 50

Expert Comment

ID: 35133551
Or, even shorter

=VLOOKUP(DATE(LEFT(\$A8,4),MID(\$A8,5,2),1),\$L\$5:\$R\$15,COLUMN(C1),FALSE)

The Column(C1) bit will return 3 in column C. When copied across, the reference will change to D1, E1, etc. returning 4, 5, etc. This will work if your datasets are in the same order in columns C to G and columns N to R.

If you use the formulas with Match() as per above, the order does not matter.

cheers, teylyn
0

LVL 50

Expert Comment

ID: 35133850
Hello,

thanks for the grade. Just as an FYI: Questions like these are normally rated with 500 points rather than 125. You will attract more experts if you assign more points to a question.

cheers, teylyn
0

## Featured Post

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.