date formula for extracting unique dates

  • I need to take a column that is in this format:
    1/1/06
    1/1/06 total
    1/4/06
    1/4/06
    1/4/06 total
    2/7/06
    2/7/06 total
    and so on


    and make it into a column heading automatically, so that any time the dates are changed or the totals move or anything, the dates at the top row automatically change with it


    1/1/06 1/4/06 2/7/06, etc


    I've been able to do it in two rows, but I can't figure out how to combine them into one formula
    I've attached that much and any help completing the task would be much appreciated

  • Re: date formula for extracting unique dates


    elfudge35, use following code:



    Good Luck! ;)

  • Re: date formula for extracting unique dates


    I want the function to go in all cells at the top and based on what is in the previous cell to the left, I want it to look for the next date on the list, look at my file


    thanks

  • Re: date formula for extracting unique dates


    I saw that, but I replied, that formula helped somewhat, but it didn't eliminate the total lines, I just want the dates

  • Re: date formula for extracting unique dates


    In your example, not all dates have a 'Total' cell. If you ensure that each date has a 'Total' cell, you can use the following formula...


    C1, copied across:


    =INDEX($A$2:$A$46,SMALL(IF(ISTEXT($A$3:$A$47),ROW($A$2:$A$46)-ROW($A$2)+1),COLUMNS($C1:C1)))


    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. See the attached file...


    Hope this helps!

  • Re: date formula for extracting unique dates


    ok, now I have a new problem, what if they are not in order, what if there are multiple pools that are separated down the first column, but I need all of the dates in order across the top row?

  • Re: date formula for extracting unique dates


    Try...


    =SMALL(IF(ISNUMBER($A$2:$A$46),IF(MATCH($A$2:$A$46,$A$2:$A$46,0)=ROW($A$2:$A$46)-ROW($A$2)+1,$A$2:$A$46)),COLUMNS($C$1:C1))


    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.


    Hope this helps!

  • Re: date formula for extracting unique dates


    ok, another problem, the function works perfectly except for the fact that it recalculates after I do anything in the workbook because it is a volatile array function and it slows me down a great deal


    is it possible to do it with a non array function?

  • Re: date formula for extracting unique dates


    Quote from elfudge35

    ok, another problem, the function works perfectly except for the fact that it recalculates after I do anything in the workbook because it is a volatile array function and it slows me down a great deal


    When you said previously that there could be "multiple pools that are separated down the first column", did you mean that there could be more than one set of the same date. For example, you could have more than one cell having '4/1/2005 Total', is this right?


    Quote

    is it possible to do it with a non array function?


    Yes. It would involve using several formulas in helper columns. Would that be okay?

  • Re: date formula for extracting unique dates


    yes, there are more than one with a certain date


    and I was hoping it could all be done with just one formula, but I'm starting to see I will need more, that's fine

  • Re: date formula for extracting unique dates


    Quote from elfudge35

    yes, there are more than one with a certain date


    Okay, that's what I understood. I just wanted to make sure...


    Quote

    I was hoping it could all be done with just one formula, but I'm starting to see I will need more, that's fine


    Great! Give a few minutes and I'll post a solution along with a sample file...

  • Re: date formula for extracting unique dates


    ok, problem with that, there are some dates that repeat again, but not all the dates that are in the other pools are in the first pool


    like this:
    pool 1
    2/21/03
    2/21/03 total
    4/1/03
    4/1/03 total
    7/1/03
    7/1/03 total
    pool 2
    2/21/03
    2/21/03 total
    5/16/03
    5/16/03 total


    then the 5/16 would come after the 7/1/03

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!