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
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.Originally Posted by wellous
Hello Jazzer,
thanks for your reply.
Yes it happens and this is what I want to detect,
i have this formula :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".
Please Login or Register to view this content.
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.
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!
I'm still having trouble understanding your criteria for marking as a duplicate.
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.
But row 114 is marked as a duplicate. What is the problem on row 114?
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.the request was opened while another request was not closed
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
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.
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
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.
Hello,
many thanks for your help,
I receive error msg. saying : Type mismatch
I feel we are too close
All the best!
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?
Hello,
it stops at this lineSaying type mismatch.Please Login or Register to view this content.
All the best!
I think I attached the wrong version of the file. Try this one.
Hello,
it's perfect now,
Working like a charm,
Many thanks
All the best!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks