Remove duplicates from row

PacVII

New Member
Joined
Jul 20, 2017
Messages
19
I have a list of names down column D and corresponding vacation days across the row, F through Z. example below
D E F G H I
NameNext day off:Scheduled Vacation Days:
Jane Smith12/1/202012/1/202012/2/202012/4/202012/4/2020
William Jones12/8/202012/8/202012/10/202012/10/202012/23/2020
John Doe1/4/202112/23/202112/24/20211/6/20211/6/2021

I get data from a report, i just copy it over into the last rows, then re-sort the dates in order. this places the dates in order across the row and sometimes it will have dates i already have on my workbook.

I am trying to run a macro across the row to remove duplicates days F through Z. just trying to remove one of the duplicate date across that row.

I found plenty of information on how to remove duplicates from a report but some of the dates maybe a duplicate as two people may have that same day off. i just want to remove duplicate from the row so the date does not show twice under the same person name.

any idea on how i can run this?

here is what i have for sorting the dates:

VBA Code:
Sub SortVacationDays()

Call DeleteOldDates

Dim X, strRow As Integer

Application.ScreenUpdating = False

strRow = 3

'Sort Rows

For X = 1 To 151
strRow = strRow + 1
Sheets("Vacation Days").Select
range(CStr("F" & (strRow) & ":AG" & (strRow))).Select
ActiveWorkbook.Worksheets("Vacation Days").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Vacation Days").Sort.SortFields.Add Key:=range(CStr("F" & (strRow) & ":AZ" & (strRow))), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Vacation Days").Sort
.SetRange range(CStr("F" & (strRow) & ":AZ" & (strRow)))
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
range(CStr("F" & (strRow))).Select
Next X

Application.ScreenUpdating = True
range("A1").Select

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,118
Office Version
  1. 365
Platform
  1. Windows
How about this? I set the range for A-F for the test data you posted. Just change the line of code to go to Z instead of F if that's where your data ends.

NameNext day off:Scheduled Vacation Days:
Jane Smith12/1/202012/1/202012/2/202012/4/2020
William Jones12/8/202012/8/202012/10/202012/23/2020
John Doe1/4/202112/23/202112/24/20211/6/2021


VBA Code:
Sub noDupes()
Dim r As Range: Set r = Range("A2:F" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant: AR = r.Value2

With CreateObject("System.Collections.ArrayList")
    For i = LBound(AR) To UBound(AR)
        For j = 3 To UBound(AR, 2)
            If .contains(AR(i, j)) Then AR(i, j) = vbNullString
            .Add AR(i, j)
        Next j
    Next i
End With

r.Value2 = AR
r.SpecialCells(xlCellTypeBlanks).Delete (xlToLeft)
End Sub
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,118
Office Version
  1. 365
Platform
  1. Windows
Just realized I missed a line of code.

VBA Code:
Sub noDupes()
Dim r As Range: Set r = Range("A2:F" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant: AR = r.Value2

With CreateObject("System.Collections.ArrayList")
    For i = LBound(AR) To UBound(AR)
        For j = 3 To UBound(AR, 2)
            If .contains(AR(i, j)) Then AR(i, j) = vbNullString
            .Add AR(i, j)
        Next j
        .Clear
    Next i
End With

r.Value2 = AR
r.SpecialCells(xlCellTypeBlanks).Delete (xlToLeft)
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,862
Members
416,345
Latest member
sayad

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
Top