Create a sequence based on a date in another column (excel)

user_14049078 0
Viewed 216

Thanks in advance for reading this.

I am looking at created a numbered sequence column that depends on the date in another column.

Picture: What I want it to look like

If you see the pic above, I want to auto-create the "Sequence" column (N) when I enter a date in the previous column (M). The number in the column is based on how small or large the date is. Example, 6th Aug is the smallest date in the column, so the sequence is 1. 8th Aug is next, so it get 2 and so on. If I enter 5th Aug in an empty cell in that column (M), the sequence should completely change to accommodate a smaller date entered. This should also hold for any date I enter, so if I enter 13th Aug, it should become 5 and 21st Aug should move over to 6!

I hope I have been able to explain what I need! Any help would be highly appreciated!

excel-formula excel
2 Answers

With dynamic array formulas:

=MATCH(M2,SORT(UNIQUE(FILTER(M:M,M:M<>""))),0)

enter image description here

user_92458530

N2, formula copied down :

=SUMPRODUCT(($M$2:$M$10<=M2)/COUNTIF($M$2:$M$10,$M$2:$M$10))

enter image description here

user_121251810
Related