Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a value only show at the uppermost row for the same ID
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a value only show at the uppermost row for the same ID
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. "luvgreen" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a value only show at the uppermost row for the same ID
try
=IF(AND(B2="Y",SUMPRODUCT(--($A$1:A2=A2),--($B$1:B2="Y"))=1),"Y","") "luvgreen" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thank both JJ and BJ. It works. Thank you so much!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make H = -1 on a worksheet, yet show H? | Excel Worksheet Functions | |||
How to make an Add-in show under Run-Macro | Excel Discussion (Misc queries) | |||
How to make first row to show on every page | Excel Discussion (Misc queries) | |||
Make an =if show - instead of 0 | Excel Worksheet Functions | |||
how do I make make my hyperlinks show the email address they are . | Excel Discussion (Misc queries) |