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

1. ## Marking all lines with same value when a second value is found

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/2/2005 Closed
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

2. ## Re: Marking all lines with same value when a second value is found

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)

"Jdull" <Jdull@discussions.microsoft.com> wrote in message
> 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

3. This formula will only put a "Yes" on orders "On Hold" and still not "Closed".

=CHOOSE(1+SUMPRODUCT((A7=\$A\$3:\$A\$7)*((\$C\$3:\$C\$7="On 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...tid=3572&stc=1

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1