How to Tag "Start" and "End" while I have repeated values in excel

Question

How to tag "START" / "END" when value repeats.

I have repeated values in Column U, I want to automatically tag "START" for the first value and "END" for the second repeated value in excel

Current Output:

ColumnU   
-6        
-5         
-5
-4
-4
-3
4
4
5
5
6
6

Expected output:

ColumnU   TAG
-6        START
-5        START
-5        END
-4        START
-4        END
-3        START
4         START
4         END
5         START
5         START
6         START
6         END

I have used the below formula, it works only for 200 records, apart from 200 it gives me empty cells of 40000 records, how I should fix it and works for all the 40000 records?

Formula:

=IF(COUNTIF($U:$U, $U2)>=2,IFERROR(CHOOSE(COUNTIF($U$2:U2,U2),"START","END"),""),"")

Answer 1

If your data is not sorted like your example:

=IF(MATCH(U2,U:U,0)=ROW(),"START","END")

Answer 2

I'm not sure why you are testing for >=2 since it appears the values either appear strictly once (start) or twice (end).

Therefor, just output Start when the count in the range thus far is 1

=IF(COUNTIF($A$1:A1,A1)=1,"Start","End")

Answer 3

IF formula works here..

=IF(A2<>A1,"START","END")

It's comparing the value to the one above it. This works as long as sorting this way is a workable solution for you. That's how your sample appears, so that's the assumption.