Create a sequence based on a date in another column

[gmlshd]

Hello All,

Thanks in advance for reading this.

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

Date Sequence 1.jpg

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!

[AliGW]

Welcome to the forum.

Have you tried the RANK function?

[CK76]

If you don't need it to be sequential.
i.e. since there are two 6-Aug-20, 8-Aug-20 = 3

Then you can use..
= RANK.EQ(M2,$M$2:$M$9,1)

Copy down.

Alternate method is to use Unique(Range) to extract unique dates in another area, use Rank on this column. Then use lookup to return applicable sequence to original.


See attached for sample using Unique.

Note: $H$2# is new array reference and references array result of UNIQUE() function in this case.

EDIT: On second thought, you can skip the look up part and just use RANK.EQ(M2,$H$2#,1)
$H$2# should be replaced with where you entered Unique() function.

[gmlshd]

Thanks for your response. I tried RANK, but did so without UNIQUE as suggested by CK76, so it did not work for me.

[gmlshd]

If you don't need it to be sequential.
i.e. since there are two 6-Aug-20, 8-Aug-20 = 3
...

Thank you so much. This is much better, but I have a small issue now. In order to make this practical, I used the UNIQUE() on the entire column as I keep adding rows and I dont want to edit the unique array every time. Now, the blank rows register as "1900-01-00" and always get the sequence 1.

Any way to fix this? I will also try some stuff in the meantime!

EDIT: Fixed it. Used the FILTER function to disregard blanks =UNNIQUE(FILTER(COL,COL<>""))

[CK76]

You are welcome

Used the FILTER function

That's one way to do it.

Another is to create dynamic named range or use Excel structured table.

If you are satisfied with the solution(s) provided, please mark the thread as solved using thread tools found at top of your initial post.