Results 1 to 2 of 2

Thread: extracting data

  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Thanked 0 Times in 0 Posts

    extracting data

    I have some data where the data is presented in day columns by year/month. In the attached example, column H is the year/month and the daily data are in columns J, N, R, V, etc. (DAY01 through DAY31).

    What I would like to do is pull out the data as a daily value, so that I have just two columns: Date and Daily Data. I can do this using the “bigger hammer” method of a lot of cutting and pasting, but there has to be a more elegant way to do this.

    Suggestions would be appreciated.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    If I understand what you want:

    If in sheet 2 you put the label "Date" in A1 and "Daily Data" in B1 (the actual labels don't matter)
    In A2 you can enter the date (format as desired)
    Jan 1, 1972
    and fill down with Jan 2, 1972; Jan 3, 1972; all the way to Feb 28, 1974 (or whatever your data goes to)

    In B2 enter the formula:
    =OFFSET(Sheet1!$H$1,MATCH(VALUE(YEAR(A2)&TEXT(MONT H(A2),"00")),Sheet1!H:H,0)-1,4*DAY(A2)-2)

    And copy it down column B to the end of the data. you should get what you want.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts