Apr 16 2022 07:54 AM - edited Apr 16 2022 07:58 AM
Hello,
I need some solution to remove duplicates but keep those which were entered with different date.
Currently I am using two macros.
First, to copy data from another workbook and add it to the last row of my current sheet (sheet contains 16 columns):
Sub Copy()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim i As Integer
Dim lastrow As Long
Dim verylastrow As Long
If MsgBox("Please make sure you upload Tableau report", vbOKCancel, "Just checking") = vbOK Then
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(FileFilter:="Comma Separated Values Files (*.csv),*.csv")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
lastrow = ThisWorkbook.Worksheets("sheet1").Range("B9000").End(xlUp).Row + 1
OpenBook.Sheets(1).Range("A2", Range("O2").End(xlDown)).Copy ThisWorkbook.Worksheets("sheet1").Range("A" & lastrow)
OpenBook.Close savechanges:=False
End If
End If
verylastrow = ThisWorkbook.Worksheets("sheet1").Range("B9000").End(xlUp).Row
For i = lastrow To verylastrow
Cells(i, 16).Value = Date
Next
Application.ScreenUpdating = True
'Process_Data2()
End Sub
Second, to remove duplicates:
Sub Process_Data2()
'
' Process_Data2 Macro
'
'
Range("A1:P2860").Select
Range("E1986").Activate
ActiveSheet.Range("$A$1:$P$1048576").RemoveDuplicates Columns:=Array(1, 2), _
'first column is user ID, second column: some data, third column is last activity with dates (when I include this filter here too many duplicates are not being removed).
Header:=xlYes
End Sub
Apr 16 2022 02:40 PM
Could you make a small sample workbook (without sensitive data) available through OneDrive, Google Drive, Dropbox or similar?
Apr 17 2022 06:14 AM
Hello @Hans Vogelaar ,
Thank you for your willingness to help!
Monday I will be at work and surely create a sample workbook.
Kind regards,
Guram
Apr 17 2022 07:14 AM
Apr 17 2022 08:48 AM
Apr 18 2022 11:12 PM