Results 1 to 8 of 8

Thread: Matching Date Values and exporting data to identified row/column

  1. #1

    Matching Date Values and exporting data to identified row/column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am new to Excel programming and formulas and im not sure how to best approach this, I have a large spreadsheet (500+ Row) that contains data as shown below. I am looking for a way to automate or formalise the following actions:

    Take the date value in the date column, and match it to the corresponding Column value, eg, 1/10/2013 matches Column OCT-13. Once determined, I want the price value put into that cell of the corresponding row. I hope the example below clarifies this because I dont know how to explain it :-). Thanks for any assistance given.
    Price Date OCT-2013 NOV-13 Dec-13 Jan-14
    5 1/10/2013 5
    6 1/1/2014 6
    9 1/11/2013 9
    15 1/12/2013 15
    Last edited by jm25; 2013-09-19 at 06:54 PM.

  2. #2
    Good afternoon,

    If what I understand is correct then this should work. Otherwise I may need some additional guidance . Hope this helps.

    Best of luck,
    Attached Files Attached Files

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hello
    Heres another possibility:

    =IF(AND(MONTH(C$1)=MONTH($B2), YEAR(C$1)=YEAR($B2)), $A2, "")

    Placed in C2 and copied down and across the table.


  4. #4
    You can use a VLOOKUP function, this function search the value that you selected in a database and returns information of the column that you chooser of this database. In myexceltutorial dot com you can learn more about this function. Subscribe and look in the Wiki
    Microsoft Excel Massive Open Online Course.
    Learn all about Microsoft excel in this Online course, with video tutorials, examples of applications and a complete functions and buttons explanation.
    Be part of our community and enjoy learning Excel.

  5. #5
    Hi

    Assuming that the data is in A1:F5 and the dates in the top row are also the 1st of each month, you could use the IF function!
    In C2, copy across and down: =IF($B2=C$1,$A2,"")

  6. #6
    @bgoree09: Thanks for that, was very helpful, I actually got your one working accross my spreadsheet Thursday night, sorry for the late response, thanks a million was really appreciated.

    Thanks everyone else for the suggestions. Theyre all helpful with regard to actually learning how excel formula work.

  7. #7
    Very good. You're very welcome. Hercules' formula does the same thing, but his is a bit more concise. Not really sure why I threw a dash in the concatenate, but it doesn't hurt anything. Credit where credit is due .

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by bgoree09 View Post
    Very good. You're very welcome. Hercules' formula does the same thing, but his is a bit more concise. Not really sure why I threw a dash in the concatenate, but it doesn't hurt anything. Credit where credit is due .
    Thanks
    One of the things I enjoy is how many different ways can be found to resolve a given problem.

Posting Permissions

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