IF Cell Duplicated, Paste Value next to the First Duplicated Cell Excel Forumla

[amrelewa]

I am a newb and looking for a excel formula help. Here is a diagram just to make it easy

------ A -------- B -------- C -------- D
1--- Jone ---- Yes ---- Nine ----------
2--- Jone --------------- Nine ---- End
3--- Peter ---- Yes ---------------- End
4--- Mic ------- Yes --------------- End
5--- Sarah ---- Yes --------------- End
6--- Adam ---- Yes ---- Nine----------
7--- Adam -------------- Nine ----- End
8--- Edward ---- Yes -------------End

I am try to create formula that will lookup for values in column A and If there is a repeated value, it past the word "Yes" next to the first duplicated value in Column B, Word "Nine" next to both duplicated values in Column C, and word "End" next to the last duplicated value in Column D. If the value is not duplicated, so it just past word "Yes" in column B and word "End" in column D.

[Haseeb Avarakkan]

Try this,

B1, copy down.

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

C1, copy down.

=IF(COUNTIF(A:A,A1)>1,"Nine","")

D1, copy down.

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

[amrelewa]

Thaaaanks