+ Reply to Thread
Results 1 to 11 of 11

identify duplicate in a range or overlapping with cirteria

  1. 10-29-2017, 03:32 AM #1
    shuaibfarouk
    shuaibfarouk is offline
    Registered User
    Join Date
    07-28-2009
    Location
    kuwait
    MS-Off Ver
    Excel 2007
    Posts
    14

    Post identify duplicate in a range or overlapping with cirteria

    hi
    I am trying to create a formula which can identify duplicate in a range or overlapping numbers as I continue to enter data below in column C and D.
    for example row 6 has value from 1 to 10.215, in row 11 if I enter from 5.25 to 8.63 formula should be able to identify the range and show that this is duplicate based on similar section and side (shown in the excel sheet).
    hopefully you can understand the problem better if you look at my excel sheet.
    Is that possible?
    Thank you in advance for your assistance
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-29-2017, 03:39 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,223

    Re: identify duplicate in a range or overlapping with cirteria

    Try this in F6 copied down:

    =IF(COUNTIFS($A$6:$A$17,A6,$B$6:$B$17,B6,$C$6:$C$17,"<="&C6,$D$6:$D$17,">="&D6)>1,"Duplicate","")
    Last edited by AliGW; 10-29-2017 at 03:43 AM.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-29-2017, 03:48 AM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,477

    Re: identify duplicate in a range or overlapping with cirteria

    Try in F6 then drag down:

    Please Login or Register  to view this content.
    Returns TRUE/FALSE
    Quang PT
    Reply With QuoteRegister To Reply
  4. 10-29-2017, 03:52 AM #4
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,477

    Re: identify duplicate in a range or overlapping with cirteria

    Dear AliGW, your COUNTIF will be perfect with slight amendment
    "<="&C6 ==>"<="&D6
    ">="&D6 ==> ">="&C6
    He/she want to hightlight all the duplicated rows, not the smaller ones.
    Reply With QuoteRegister To Reply
  5. 10-29-2017, 04:10 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,223

    Re: identify duplicate in a range or overlapping with cirteria

    No, that will flag the first of the two duplicates instead of the second, but not both. Your version is better.

    EDIT: Oh, you mean this?

    =IF(COUNTIFS($A$6:$A$17,A6,$B$6:$B$17,B6,$C$6:$C$17,"<="&D6,$D$6:$D$17,">="&C6)>1,"Duplicate","")

    Yes, that does it.
    Last edited by AliGW; 10-29-2017 at 04:13 AM.
    Reply With QuoteRegister To Reply
  6. 10-29-2017, 04:11 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,223

    Re: identify duplicate in a range or overlapping with cirteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  7. 10-29-2017, 04:23 AM #7
    shuaibfarouk
    shuaibfarouk is offline
    Registered User
    Join Date
    07-28-2009
    Location
    kuwait
    MS-Off Ver
    Excel 2007
    Posts
    14

    Post Re: identify duplicate in a range or overlapping with cirteria

    guys your awsome
    i thought that might not be even possible.
    formula is working fine, with minor issues, sometime it does not flag the duplicate.

    please see the attached file.

    thanks in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 10-29-2017, 04:25 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,223

    Re: identify duplicate in a range or overlapping with cirteria

    You are using the wrong version of the formula. Use this instead:

    =IF(COUNTIFS($A$6:$A$22,A6,$B$6:$B$22,B6,$C$6:$C$22,"<="&D6,$D$6:$D$22,">="&C6)>1,"Duplicate","")

    See posts #4 and #5.
    Reply With QuoteRegister To Reply
  9. 10-29-2017, 04:34 AM #9
    shuaibfarouk
    shuaibfarouk is offline
    Registered User
    Join Date
    07-28-2009
    Location
    kuwait
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: identify duplicate in a range or overlapping with cirteria

    your great.
    thanks
    best wishes.
    thumbs up (cant find the emoji)
    Reply With QuoteRegister To Reply
  10. 10-29-2017, 04:35 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,223

    Re: identify duplicate in a range or overlapping with cirteria

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  11. 11-01-2017, 05:32 AM #11
    shuaibfarouk
    shuaibfarouk is offline
    Registered User
    Join Date
    07-28-2009
    Location
    kuwait
    MS-Off Ver
    Excel 2007
    Posts
    14

    Post Re: identify duplicate in a range or overlapping with cirteria

    hi AliGW

    can you help me with the same formula again.
    formula seems to be not working as i expected.
    if in first row i enter values 1 to 2, and in 2nd row if i enter 2 to 3. formula shows this as a duplicate range, whereas this should not be duplicate.
    i am attaching my working file here, please check. file name is (Road median barriers (working file).xlsx)
    hope fully that will be better for understanding.
    thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to find overlapping duplicate values within a range.
    By VirtualDementia in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-12-2012, 12:59 AM
  2. Identify and count overlapping date/time entries on a call log
    By Andrew989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-23-2012, 12:24 PM
  3. Identify duplicate roes in a named range
    By SSGMiami in forum Excel General
    Replies: 3
    Last Post: 03-24-2009, 08:47 AM
  4. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 PM
  5. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  6. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  7. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1