Excel, show duplicates

[Jazzman]

Hi!


I have an Excel database with following columns A= Date, B= start (hrs), C= End (hrs), and D= Person (worker). Rows about 1000, sometimes less.


I have a inputsheet where I write data (date, time, and worker). The data will be stored on another sheet. I use this VBA-formula:

Range("jobline1:jobline45").Copy Destination:= _

Sheets(3).Range("A10000").End(xlUp)(2, 1)

to put the data.


Now I want to find out if there are duplicates, and in this case duplicates for the worker.


Example:

February 28, starts 8:00 ends 12:00, Bill

February 28, starts 9:00 ends 13:00, Mark

February 28, starts 9:30 ends 13:00, Bill


As we can see, Bill should be doing 2 works at the same time (8:00-12:00 and 9:30 to 13.00)and that is not possible irl.


Let's say that I want a Formula in column E that say's "Overlap".


I'm stuck with this problem.


Can somebody help me?

 

[jason]

i can see how some nested if functions may be able to accomplish this, but not sure if i can whip something up right now... i'll report back if i get anywhere.


is it wrong to assume that 1 person would only have 1 entry per day!?! (if so, that would make this much easier)

 

[Jazzman]

The problem is that one person can have 1 to 10 entries per day, normally about 5-6 entries. And when the company have 10 emplyoees, and everyone normally have 6 entries per day = 60 entries totally! Not easy to come up with a working formula.


For example, lets take a normal MONDAY. (weekday-starts at- ends at-person):

Monday 7-10 Bill

Monday 7-11 Mike

Monday 7-11 Chuck

Monday 7-11 Jennifer

Monday 7-11 Bob

...

Monday 10-12 Bill

Monday 11-13 Mike

Monday 11-13 Chuck

Monday 11-13 Jennifer

Monday 11-13 Bob

...

Monday 11-15 Bill

Monday 13-16 Mike

Monday 12-16 Chuck

Monday 13-16 Jennifer

Monday 13-15 Bob

...

Not to mention the problem that occurs when Mike can't do his 13-16 session and we have to find somebody else instead of Mike!!

 

[NARAYANK991]

Hi ,


There is a complete discussion on this subject here :


http://www.linkedin.com/groups/Ideas-shortening-massive-formula-that-3843467.S.216410614?view=&srchtype=discussedNews&gid=3843467&item=216410614&type=member&trk=eml-anet_dig-b_pd-ttl-cn&ut=2b8Prs696ah5E1


Borrowing from Tushar Mehta's reply , you can use the following formula , entered as an array formula , using CTRL SHIFT ENTER :


=MAX(IF(ROW($K$2:$K$16)<>ROW($K2),IF($D$2:$D$16=$D2,ROW($K$2:$K$16)))*($J2<$K$2:$K$16)*($K2>$J$2:$J$16))


This assumes that you can convert the values of start and end hours into time values , by using the =TIME(hrs,0,0) function.


Columns J and K in the above formula refer to the converted to time values of start and end.


If there is an overlap , the result will indicate a row number where there is an overlap ; if not , the result will be 0.


Narayan