+ Reply to Thread
Results 1 to 3 of 3

formula to determine value appears more than once based on crteria

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,726

    formula to determine value appears more than once based on crteria

    I have for values in Col D and Branch + "PL" or Branch + "statement" in Col B eg BR1 PL or BR1 Statement

    I need to set up a formula so that where the value is the same and the branch in Col B contains "PL" and "Statement" for the same value then display "Delete" , otherwise blank


    I have shown what the result should look like in Col F by manually inserting Delete


    your assistance in this regard is most appreciated
    Last edited by Howardc1001; 07-14-2021 at 02:40 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: formula to determine value appears more than once based on crteria

    =IF(COUNTIFS($B$2:$B$6,SUBSTITUTE(B2,"PL","Statement"),$D$2:$D$6,D2),"Delete","")

    Note:

    It works for 85.05 vs 85.05

    It does not work for 85.05 vs (40 and 45.05 or 30 and 55.05,...) (If this is probably the actual case)
    Quang PT

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,726

    Re: formula to determine value appears more than once based on crteria

    Thanks for the help. The formula works 100%

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] sumifs formula to extract first left item based on Crteria
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-15-2019, 01:20 AM
  2. [SOLVED] Need a formula to determine priority based on rank
    By Nlambert in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-30-2017, 06:01 AM
  3. Replies: 4
    Last Post: 08-28-2017, 07:35 PM
  4. Formula to determine the due date based on several criteria
    By helmi_jusuf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-07-2015, 07:41 PM
  5. Formula that returns the contents of a cell based on where a value appears
    By shieldsp1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-18-2010, 06:45 PM
  6. Replies: 2
    Last Post: 01-16-2010, 01:57 PM

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