+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Jdull
    Guest

    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/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

  2. #2
    N Harkawat
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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