Solved

Converting mothly data to daily data

Posted on 2011-03-14
5
290 Views
Last Modified: 2012-05-11
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
Comment
Question by:pwflexner
  • 4
5 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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

by:
Ingeborg Hawighorst 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

by:sshah254
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

by:Ingeborg Hawighorst
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

by:Ingeborg Hawighorst
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now