+ Reply to Thread
Results 1 to 6 of 6

Using index to return data from list for last 12 months or months available

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Using index to return data from list for last 12 months or months available

    Hi, I have several columns of data. The first column in the list is a column of dates. Basically, what i want to do is return the last 12 months of dates and associated data. Now, keep in mind that the column of dates may not contain 12 months of data so you just return all months shown and associated data.

    I have enclosed a file to help show what i want because trying to describe it via words is kinda hard.

  2. #2
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Using index to return data from list for last 12 months or months available

    for got to attach the file........
    Attached Files Attached Files

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using index to return data from list for last 12 months or months available

    One way could be this.

    In R2 and copy down(you can hide this column).

    =COUNTIF($O$2:O2,O2)

    Then in U2 and copy down, use this ARRAY formula.

    =IFERROR(INDEX($O$2:$O$13,SMALL(IF($R$2:$R$13=1,ROW($O$2:$O$13)-1),ROW(O1))),"")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Using index to return data from list for last 12 months or months available

    thanks.......but is there a way to do it with a single formula that is NOT an array formula?

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Using index to return data from list for last 12 months or months available

    ok...this is what i finally came up with........see enclosed file.........seems to work. Soln is in cols y through AJ.


    not sure its the best one but it does seem to work.
    Attached Files Attached Files

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using index to return data from list for last 12 months or months available

    Quote Originally Posted by welchs101 View Post
    not sure its the best one but it does seem to work.
    No one is never sure that his own is the best one!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1