Search code examples
excelexcel-formulaexcel-2010excel-2007

How arrange dates from min to max in columns based on unique id column value in excel

This is given data in excel enter image description here

I want it in this format enter image description here

Arrange dates in min to max based on unique ids in excel.

Solution

  • EDIT

    If you are using O365, then you may try using in this way as well,

    Formula used in cell D2

    =UNIQUE(A2:A10)

    Formula used in cell E2

    =TRANSPOSE(SORT(FILTER($B$2:$B$10,D2=$A$2:$A$10),,1))

    For Excel Users 2007 & 2010 & onwards,

    Formula used in cell D2 - To get UNIQUE id

    =IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$10),0)),"")

    Formula used in cell E2 --> To get sorted in ascending order dates based on ID,

    =SMALL(IF($D2=$A$2:$A$10,$B$2:$B$10,""),COLUMN(A1))

    Since its an array formula, you need to press CTRL SHIFT ENTER based on your excel version,

    Or you may use this formula as well

    =AGGREGATE(15,6,($B$2:$B$10)/($D2=$A$2:$A$10),COLUMN(A1))

    ARRANGE_DATES