+ Reply to Thread
Results 1 to 14 of 14

Find if a request was opened twice or more using duplicates and request date

  1. #1
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Question Find if a request was opened twice or more using duplicates and request date

    Dear Experts,

    Please help,
    I have a list of requests backlog, Attached
    Columns ( request-id, Creation date, Closure date, Device item)

    I would like to see if any of the requests were opened or logged twice or more, using the duplicates in (Device item) and the (Creation Date) of the duplicate to be before the last (Closure date) of same Device Item.

    any help by formula or VBA code would be appreciated
    all the best!
    Wellous
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,834

    Re: Find if a request was opened twice or more using duplicates and request date

    Quote Originally Posted by wellous View Post
    using the [...] the (Creation Date) of the duplicate to be before the last (Closure date) of same Device Item.
    Is it possible for a Creation Date for a Device Item to be later than the latest Closure Date for that item? It doesn't seem to me that you have to check for that.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Find if a request was opened twice or more using duplicates and request date

    Hello Jazzer,
    thanks for your reply.
    Yes it happens and this is what I want to detect,
    i have this formula :
    Please Login or Register  to view this content.
    which is not working always, (I.e: row #114 in the snapshot above) the request was opened while another request was not closed.... this is what I want to capture and mark as "duplicate req".

    Also I have a Macro in the attached file, which is capturing the duplicate Device Item only without looking on the dates

    Thanks again
    Last edited by wellous; 04-15-2020 at 08:02 AM.

  4. #4
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Find if a request was opened twice or more using duplicates and request date

    to avoid confusion, row #113 shouldn't be a duplicate as the 2nd request was created after the closure of the last request.
    but row#114 is a correct duplicate.

    sorry for the confusion,
    All the best!

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,834

    Re: Find if a request was opened twice or more using duplicates and request date

    I'm still having trouble understanding your criteria for marking as a duplicate.

    Quote Originally Posted by wellous
    the (Creation Date) of the duplicate to be before the last (Closure date) of same Device Item.
    In your screenshot, the last closure date for this device item is 03-02-2020 15:45 (I assume that format is mm-dd-yyyy). All creation dates are before that closure date. That's not what your formula says to do, but I don't see a problem in the results.

    Quote Originally Posted by wellous View Post
    which is not working always, (I.e: row #114 in the snapshot above) the request was opened while another request was not closed.... this is what I want to capture and mark as "duplicate req".
    But row 114 is marked as a duplicate. What is the problem on row 114?

    the request was opened while another request was not closed
    This is different than what you described in the first post, can you please clarify your criteria for marking as a duplicate? It might help if you explain the real-world result you are trying to get.

  6. #6
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Find if a request was opened twice or more using duplicates and request date

    Hello Jazzer,
    let me try to explain,

    1st criteria is to find the same Device item
    2nd criteria: if more than one request is created in the same time.

    we can know that by looking on the creation date and closure date for the same Device item if 2nd request was created after the 1st is being closed then fine ... this is not a duplicate
    but if the 2nd request was created while we are still working on the 1st one then it means duplicate and should be marked in the row.

    In the snapshot above :

    Row 113 is not duplicate as the request was created on: (03-Jan-2020 @05:08) after the last request was closed on (03-Jan-2020 @05:05) which is the row(#46) , so after we closed the 1st req with 3 minutes .... ok not duplicate which demonstrate that my formula is wrong it shouldn't show duplicate here


    Row#114 is a duplicate as the request was created on : (03-Jan-2020 @05:08) while the previous request was still opened as you can see in row #113 and its closure was on (03-Jan-2020 @07:50)

    so I want to mark those requests as duplicate if possible,

    many thanks for your help

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,834

    Re: Find if a request was opened twice or more using duplicates and request date

    So want to identify multiple requests for the same item that are all open at the same time. I do not know how to do that with formulas but let me look at a VBA solution.

  8. #8
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Find if a request was opened twice or more using duplicates and request date

    Hello Jazzer,

    yes please, i have a Macro in the attached file, but the code is only finding the Device item duplicate without looking into the dates to facilitate which ones are open at the same time.
    i believe it needs to Dim opendate1, closerdate1,opendate2
    map Opendate1 to Creation date
    map closerdate1 to Closure date
    map opendate2 to (( 2nd request creation date)) or (( Opendate1 -1)



    then for each duplicate Device item where opendate2< Closerdate1 to mark as duplicate.

    many thanks,
    Wellous

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,834

    Re: Find if a request was opened twice or more using duplicates and request date

    I have added Sub IdentifyDuplicates to the Sheet5 module. I added a column in H to indicate the duplicates.

    The criteria for duplicates is same Item number, and overlap of Creation to Closure.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Find if a request was opened twice or more using duplicates and request date

    Hello,

    many thanks for your help,
    I receive error msg. saying : Type mismatch
    I feel we are too close
    All the best!

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,834

    Re: Find if a request was opened twice or more using duplicates and request date

    I don't get an error. What line of code is highlighted when the error occurs? What is the last line shown in the Immediate Window when the error occurs?

  12. #12
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Find if a request was opened twice or more using duplicates and request date

    Hello,
    it stops at this line
    Please Login or Register  to view this content.
    Saying type mismatch.

    All the best!

  13. #13
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,834

    Re: Find if a request was opened twice or more using duplicates and request date

    I think I attached the wrong version of the file. Try this one.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-19-2011
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Find if a request was opened twice or more using duplicates and request date

    Hello,
    it's perfect now,
    Working like a charm,
    Many thanks
    All the best!

+ 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] find the date close to that request
    By Berna11 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2016, 03:09 PM
  2. Wait for winhttp request to be done (much like XML http request)
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-23-2015, 09:26 AM
  3. Request for Formula to find out True Or False
    By umesh.dange in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2014, 09:43 AM
  4. VBA Code Request for tracing duplicates on invoice
    By excelvba123 in forum Excel General
    Replies: 2
    Last Post: 05-06-2012, 06:46 AM
  5. Excel Find & Replace Macro Request Help
    By ganeshinscribe in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2012, 09:21 AM
  6. Macro request: Create and Search two sets; return number of duplicates
    By Breex243 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2010, 05:46 PM
  7. [SOLVED] Where do I find the Payment Request Wizard in Outlook?
    By nonprofitgirl in forum Excel General
    Replies: 0
    Last Post: 05-18-2005, 07:06 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