Finding duplicates in rows of dates against the leave codes

[jaysbp]

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?

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

 

[kevin9999]

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
	V	W	X	Y	Z	AA	AB	AC	AD
1	Days Blocked
2	VL	21/12/2022	22/12/2022	26/12/2022	27/12/2022
3	SBL	22/12/2022	26/12/2022	27/12/2022	28/12/2022	29/12/2022
4	AL	26/12/2022	27/12/2022	28/12/2022	29/12/2022	30/12/2022
5	VL	8/12/2022	9/12/2022	12/12/2022	13/12/2022	14/12/2022	15/12/2022	16/12/2022
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W2:AD5	Cell Value	duplicates	text	NO

 

[jaysbp]

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.

 

[kevin9999]

Where in the range from column V onwards do you indicate which employee the leave dates apply to?

 

[jaysbp]

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!

 

[kevin9999]

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.

 

[jaysbp]

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!

 

[kevin9999]

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?

 

[jaysbp]

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.

I have been mulling over this over a week and just cant seem to get it right. Thanks again for guiding me on this !

 

[kevin9999]

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.