Marking all lines with same value when a second value is found

J

Jdull

I have a excel 2003 spreadsheet with a list containing order number, date,
status. I want to create a function to give a value in a new field for all
order numbers if one of the statuses is 'on hold'

My data would be like below
Order# Date Status
1 7/1/2005 Received
1 7/2/2005 Closed
2 7/1/2005 Received
2 7/2/2005 On Hold
2 7/5/2005 Closed

In the new field I would like all that lines for order 2 to have a "Yes"
value in the new field because one of the status was On Hold
 
N

N Harkawat

say your data is in the range A2:C6 and you want "Yes" on column D then on
cell D2 type the formula below and fill all the way down to cell D6

=IF(ISERROR(MATCH(1,($C$2:$C$6="On Hold")*($A$2:$A$6=A2),0)),"","Yes")
array entered(ctrl+shift+enter)
 
O

olasa

This formula will _only_ put a "Yes" on orders "On Hold" and still no
"Closed".

=CHOOSE(1+SUMPRODUCT((A7=$A$3:$A$7)*(($C$3:$C$7="O
Hold")+($C$3:$C$7="Closed")*3)),"","Yes","","","")

Hope it can be of use
Ola Sandström


Also see encl. zip-file
http://www.excelforum.com/attachment.php?attachmentid=3572&stc=

+-------------------------------------------------------------------
|Filename: Book5.zip
|Download: http://www.excelforum.com/attachment.php?postid=3572
+-------------------------------------------------------------------
 
Top