Forum Discussion

bxsilva1740's avatar
bxsilva1740
Copper Contributor
Nov 03, 2020

Excel Formula to find and place Unique Value, calculate days between the first entry and last entry

In the table below I require to find formula to fill out column C and D, since I have thousand of records to filter through COLUMN A has duplicate ID which I need to identify the distinct ID in CO...
Formulas and Functions
bxsilva1740's avatar
bxsilva1740
Copper Contributor
Nov 03, 2020
Thanks Riny_van Eekelen; did not work for me. This is what I see. Not supported.

=MAX(_xlfn._xlws.FILTER(B3:B11,(A3:A11=A15)))-MIN(_xlfn._xlws.FILTER(B3:B11,(A3:A11=A15)))

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 03, 2020

    bxsilva1740 

    As variant

    C3:
=IFERROR(
  INDEX($A$3:$A$11,
        AGGREGATE(15,6,1/(COUNTIF($A$3:A3,A3)=1)*(ROW()-ROW($A$2)),1)
  ),
"")

D3:
=IFERROR(
  AGGREGATE(14,6,1/(COUNTIFS(C3,$A$3:$A$11))*$B$3:$B$11,1)-
  AGGREGATE(15,6,1/(COUNTIFS(C3,$A$3:$A$11))*$B$3:$B$11,1),
"")

    and drag down

Resources