Reputation: 21

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

Thanks in advance for reading this.

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

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!

Upvotes: 2

Answers (3)

Sridharan

Reputation: 13

Use this formula:

=COUNTIF(AA$10:AA10,AA10)+COUNTIF($AA$10:$AA$17,"<"&AA10)

Upvotes: 0

bosco_yip

Reputation: 3802

N2, formula copied down :

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

Upvotes: 0

BigBen

Reputation: 50007

With dynamic array formulas:

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

Upvotes: 2

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

Answers (3)

Related Questions