Forum Discussion
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...
Here's a method. I'll be curious if other more elegant solutions are proposed.
This does presume that you've sorted all of the records by the first column (the ID)... Assuming that's the case, then this formula
=IF(A3<>A2,A3,"")
will populate column C with the new ID each time it changes; otherwise leave it blank.
And then, for each row in column C that has a new ID, this formula in column D subtracts the minimum date from the maximum date for the ID in the adjacent column.
=IF(C3="","",MAXIFS($B$3:$B$11,$A$3:$A$11,C3)-MINIFS($B$3:$B$11,$A$3:$A$11,C3))
See the attached
- Thanks for your reply; works great.
