in excel leave

[JIGYANSHA1985]

Hello Sir,

I have a question for you in excel, If you dont mind please solve it.

In the attached excel sheet, sample leave records are there.... I
would like to know that how to avoid record duplicacy as highlighted
in the sheet1 i.e. same empcode tries to take leave and date also
falls dt_from - dt_to ranges. How to avoid it. How to count days....

With Regards
Jigyansha

At the time of attaching the file error encounter so please let me know your email id so that I can forward the attached sample to you ...

[steveh]

Hello Sir,

I have a question for you in excel, If you dont mind please solve it.

In the attached excel sheet, sample leave records are there.... I
would like to know that how to avoid record duplicacy as highlighted
in the sheet1 i.e. same empcode tries to take leave and date also
falls dt_from - dt_to ranges. How to avoid it. How to count days....

With Regards
Jigyansha

At the time of attaching the file error encounter so please let me know your email id so that I can forward the attached sample to you ...

Hi There

The attachment must be 100kb or below so please attach an editted version that is below this, you can also attach a zip thta is 100kb or below and then somebody will be able to take a look for you.

[HansV]

Actually, attachments here can be up to 256 KB.

[steveh]

Actually, attachments here can be up to 256 KB.

O

Quite often I could have posted a workbook but couldn't get it down as low as I thought it had to be. I wonder where I got that number from, the old Woody's perhap?

[HansV]

Yes, the limit was 100 KB in Woody's Lounge.

[StuartR]

Jigyansha,

Welcome to Eileen's lounge.

If your Excel spreadsheet is very large then please try to remove some of the data to make it smaller and then you will be able to attach it to a post.

If this is not the problem then please let us know the EXACT error message you got when trying to add the attachment.

[JIGYANSHA1985]

Hello Sir,

I have a question for you in excel, If you dont mind please solve it.

In the attached excel sheet, sample leave records are there.... I
would like to know that how to avoid record duplicacy as highlighted
in the sheet1 i.e. same empcode tries to take leave and date also
falls dt_from - dt_to ranges. How to avoid it. How to count days....

With Regards
Jigyansha

[HansV]

Try this macro:

Code: Select all

Sub RemoveDups()
  Dim r As Long
  Dim m As Long
  m = Range("A" & Rows.Count).End(xlUp).Row
  Range("A2:G" & m).Sort _
    Key1:=Range("A2"), Key2:=Range("B2"), Key3:=Range("D2"), _
    Header:=xlYes
  For r = m - 1 To 3 Step -1
    If Range("A" & r) = Range("A" & (r + 1)) And _
        Range("B" & r) = Range("B" & (r + 1)) And _
        Range("E" & r) = Range("D" & (r + 1)) Then
      Range("E" & r) = Range("E" & (r + 1))
      Range("F" & r) = Range("F" & r) + Range("F" & (r + 1)) - 1
      Range("A" & (r + 1)).EntireRow.Delete
    End If
  Next r
End Sub

Make a copy of the workbook before testing!

[JIGYANSHA1985]

Thanks Sir For your quick reply but rather I require an active macro that will not permit entering duplicate data within that period ... The code that you provide works fine but the deleting the records ... But later when I enter new duplicate records it was accepting the same...

[HansV]

In the attached version, I have applied Data Validation with a custom formula

=AND($E3>=$D3, SUMPRODUCT(($B$3:$B$27<>"") * ($B$3:$B$27=$B3) * ($D$3:$D$27<=$E3) * ($E$3:$E$27>=$D3))<=1)

to the range D3:E27. The condition

$E3>=$D3

checks that the "To" date is on or after the From" date, and

SUMPRODUCT(($B$3:$B$27<>"") * ($B$3:$B$27=$B3) * ($D$3:$D$27<=$E3) * ($E$3:$E$27>=$D3))<=1 checks that there is at most one row with the non-blank ECODE and overlapping From-To periods.

The number of days is calculated by a formula:

=IF(OR(D3="",E3=""),"",E3-D3+1)

LEAVE.xls

[JIGYANSHA1985]

Sir, on given excel sheet when I am entering some data at row 8 and F column some error like #VALUE! comes. But as per criteria if from date and to date comes or falls for the same ecode earlier then it will not accept the value. But here there is no duplicacy but error comes at row 8 & F column ... Pl. sort out this ...

5559 22791 MIKE 24/08/2010 26/08/2010 #VALUE! CL

[HansV]

Make sure that you enter the dates according to your system settings. On the PC I'm currently, using, the system date format is m/d/yyyy, and it works correctly:

Capture1.png

[JIGYANSHA1985]

Alright sir, I am using the same in DD/MM/YYYY format earlier, right now as per your instruction its working correctly. But if I do the same in DD/MM/YYYY format what to do ?

Thank you very much Sir for spending so much time with me ...

[HansV]

It should work with your system date format. If I set my system date format to dd/mm/yyyy, it still works:

Capture1.png

Make sure that your cells aren't using text format.

[JIGYANSHA1985]

Thanks Sir, its working ...