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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

luvgreen - 18 Jun 2007 19:54 GMT

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

Reply to this Message

JJ - 18 Jun 2007 20:24 GMT

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.

> Hi all:
> I have this problem. I want to write a formula in award column, so that the
[quoted text clipped - 11 lines]
> 2 Y
> 2 Y

Reply to this Message

bj - 18 Jun 2007 20:25 GMT

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

> Hi all:
> I have this problem. I want to write a formula in award column, so that the
[quoted text clipped - 11 lines]
> 2 Y
> 2 Y

Reply to this Message

luvgreen - 18 Jun 2007 20:35 GMT

Reply to this Message