Finding duplicates in rows of dates against the leave codes

jaysbp

New Member
Joined
Nov 17, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi everyone, I am having difficulty in getting this code right. If you look at my screenshot, From columns L to T , I will have rows of data of leaves being applied by employees. They will have multiple rows of different leave types as they apply leave (from column L to T). From column W onwards, I will press the command button "Transfer Now" and it will spill out the dates horizontally without including weekends and holidays. I have gotten this code right.However, I need to write in a code to check from column W onwards only (in terms of row) to check the dates applied by the employee for different leave types on whether there is any duplicate date picked. For eg, if you can see in the screenshot, employee A has applied same dates like (22/12/2022) for VL (Cell X2) and SBL (Cell W3). I want the code to highlight the duplicate values for the respective employee. Can someone help me on this?

Screenshot.jpg

VBA Code:
Sub checkforrepeateddates()

'Declare All Variables.
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Lee Hyunsoo (3)")
    

With ws
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count


'Loop Each Row To Check Duplicate Values and Highlight cells.
For i = 2 To myRow
Set myRange = Range(Cells(i, 2), Cells(i, myCol))
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
Next
End With

End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
I wouldn't use code for this when you could simply use Conditional Formatting - Highlight Cell Rules - Duplicate Values (and set the format to whatever you want). You get this:

jasbp.xlsx
VWXYZAAABACAD
1Days Blocked
2VL21/12/202222/12/202226/12/202227/12/2022
3SBL22/12/202226/12/202227/12/202228/12/202229/12/2022
4AL26/12/202227/12/202228/12/202229/12/202230/12/2022
5VL8/12/20229/12/202212/12/202213/12/202214/12/202215/12/202216/12/2022
6
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W2:AD5Cell ValueduplicatestextNO
 
Upvote 0
hi , thanks for your reply. I have only shown the results of employee A in my screenshot. How about if there are employees B and C so on...and their dates coincide with the dates that employee A has applied.How can I make the conditional formatting unique to each employees and if they apply for duplicate leaves? For eg , employee C applied for 27th Dec annual leave (AL) , the conditional formatting rule should not pick out this as duplicate as it is unique to employee C.
 
Upvote 0
Where in the range from column V onwards do you indicate which employee the leave dates apply to?
 
Upvote 0
Hi, sorry I forgot to indicate that - how about column U - if I lookup the employees' names in this column U - how can i make distinct conditional formatting based on employees and responding leave codes? Thanks so much!
 
Upvote 0
Could you provide a sample of what your table will look like using the XL2BB add in? Alternatively, if you could share your file via Dropbox, Google drive or something similar.
 
Upvote 0
Hi! I am very sorry for my late reply. I fell ill and was unable to continue with work. I have attached my file. Did a simple one .
Thanks for helping me in teaching how we can exclusively conditional format unique to the employee.
However, can i request for an additional help or insight how i can do this step. So if you look into my file, I have the dates spilled out (excluding weekend and holidays in columns X to AW.
  • Is there any way, I can consolidate these dates under the respective months showing from columns L to W( row 14 onwards) for the respective employees A and B. I was thinking about text join using vba codes as the end user ultimately is not good with excel.I have spent way too much time but i really can't seem to get it right.
  • master data (link to my excel file in google)
Your is help is greatly appreciated!
 
Upvote 0
Is there any way, I can consolidate these dates under the respective months showing from columns L to W( row 14 onwards) for the respective employees A and B.
How exactly do you want them to "show" in the consolidated cells?
 
Upvote 0
Hi , I have included a screenshot of the a simple consolidation that I did with using text join manually (I have circled the cell in blue). Eventhough my formula states to ignore empty cells not sure why it is picking up the empty cells that i have highlighted. I would like to place a command button at the side and allow the user to click and it should dynamically consolidate the dates (column x to aw) to the leave codes and respective employee shown on columns w and v respectively under the various column of months from row 14 onwards.
So for eg , if you can see in my file/screenshot in cell L15 - it shows the consolidates of dates by applied by employee a for annual leave (AL). This corresponds to the results generated in columns x to aw. So if the employee has multiple row of AL applied (in rows x to aw), the formula should be able to consolidate the range for eg(x to aw) and match to the leave code and employee and consolidate the respective dates from row 14 onwards under the respective months.
1669529565635.png


I have been mulling over this over a week and just cant seem to get it right. Thanks again for guiding me on this !
 
Upvote 0
I've struggled with this for a couple of hours now and I'm afraid that I'm not making any progress. Hopefully someone else on the forum will come to the rescue. Good luck.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top