How to make a value only show at the uppermost row for the same ID

[luvgreen]

Hi all:
I have this problem. I want to write a formula in award column, so that the
"Y" only appears on the first occurance of when user ID have Y in Data, no
other place after first occurance. How can I do that? Hope you can give me
some advice. Thanks much.

ID Data Award
1 Y Y
1 N
1 Y
1 Y
2 N
2 Y Y
2 Y
2 Y

 

[JJ]

Don't know if it is the most elegant solution, but creat a hidden column with
ID & Data concatenated. Order your data by ID then by Data. Your award
formula is if(and(Data="Y",Data(row current)<>Data(row previous)),"Y",""

in C2 = A2 & B2
Order by ID then Data ascending
d2 = if(and(B2="Y",D2<>D1),"Y",""
copy C2 and D2 down to the end of your data
hide column C

Maybe someone will have a better solution. Good luck.

 

[bj]

try
=IF(AND(B2="Y",SUMPRODUCT(--($A$1:A2=A2),--($B$1:B2="Y"))=1),"Y","")

 

[luvgreen]

Thank both JJ and BJ. It works. Thank you so much!!