Forum Discussion
Excel Formula to find and place Unique Value, calculate days between the first entry and last entry
Here's a more elegant solution, in that it will list all the Unique IDs all bunched together...you could even put these formulas on their own page, apart from the database itself.
The use of UNIQUE does require the most recent edition of Excel. If you have that edition, you will find this preferable to the prior solution.
Put this wherever you want it. I illustrate it in Column C, but it could be placed elsewhere.
=UNIQUE(A3:A11)
UNIQUE will "spill" the results down as far as necessary to produce a complete list, so there should be no other content below it.
This formula remains as it was before, and should be adjacent to the foregoing.
=IF(C3="","",MAXIFS($B$3:$B$11,$A$3:$A$11,C3)-MINIFS($B$3:$B$11,$A$3:$A$11,C3))
mathetes Thanks Mathetes. the suggested formula did not work; the result of the formula was 0.
Also looking for a formula for D3 to compute the number of days between the first date for the ID and last date for the same ID.
I attached spreadsheets in both cases that DID work. I just posted the formula to make the posting more educational.
The second example, using UNIQUE, will not work, as I said, unless you have the newest versions of Excel.
In your file ID are numbers, and Distinc ID are texts first character of which is non-breaking space (unicode 160), most probably they are copy/pasted from Web.
For this concrete sample that could be fixed as
=MAXIFS($B$3:$B$11,$A$3:$A$11,SUBSTITUTE(C3,UNICHAR(160),"")*1)- MINIFS($B$3:$B$11,$A$3:$A$11,SUBSTITUTE(C3,UNICHAR(160),"")*1)bxsilva1740 If you are on a modern version of excel, perhaps the attached sheet answers your question.
- Thanks; works great.
- 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)))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
