Macro to remove duplicates & then order list by date

[saitken]

Hello. Can anyone help me with a Macro that copies a list of data from Sheet 1 to Sheet 2 and then removes duplicates and orders the list by 'Finish Date'.
Please see my data and the output I want.
Data in Sheet 1.
Copy to Sheet 2.
Remove the duplicates from 'Description' column in Sheet 2.
Then order the list by 'Finish Date', oldest to newest in Sheet 2
Thank you

 

[DanteAmor]

Try this:

Sub remove_duplicates_order_by_date()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr As Long
  
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  lr = sh1.Range("G" & Rows.Count).End(3).Row
  
  sh2.Range("A2").Resize(lr - 4).Value = sh1.Range("G5:G" & lr).Value
  sh2.Range("D2").Resize(lr - 4).Value = sh1.Range("AV5:AV" & lr).Value
  sh2.Range("E2").Resize(lr - 4).Value = sh1.Range("AW5:AW" & lr).Value
  With sh2.Range("A1:E1").Resize(lr - 3)
    .RemoveDuplicates Columns:=Array(1, 4, 5), Header:=xlYes
    .Sort sh2.Range("E1"), xlAscending, Header:=True
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[saitken]

Sub remove_duplicates_order_by_date() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr As Long Set sh1 = Sheets("Sheet1") Set sh2 = Sheets("Sheet2") lr = sh1.Range("G" & Rows.Count).End(3).Row sh2.Range("A2").Resize(lr - 4).Value = sh1.Range("G5:G" & lr).Value sh2.Range("D2").Resize(lr - 4).Value = sh1.Range("AV5:AV" & lr).Value sh2.Range("E2").Resize(lr - 4).Value = sh1.Range("AW5:AW" & lr).Value With sh2.Range("A1:E1").Resize(lr - 3) .RemoveDuplicates Columns:=Array(1, 4, 5), Header:=xlYes .Sort sh2.Range("E1"), xlAscending, Header:=True End With End Sub

Superb. Thanks you very much. Worked first time.
Stewart

 

[saitken]

Hi. I have realised that I need this brilliant Macro to be tweaked.
I have two sets of data identified as 'Phase 1' and 'Phase 2'. Each identified with an 'x' in columns I & J.
I want to be able to run the macro for 'Phase 1' data, which has an 'x' in column I.
And then run it again for 'Phase 2' data, which has an 'x' in column J.

Sheet 1:

Sheet 2 (required output):

How would I update your Macro to do this please?

Thank you

 

[DanteAmor]

Hi saitken.
You should mark the post that actually solved your original problem. Unless your own answer is the solution.

 

[saitken]

Hi saitken.
You should mark the post that actually solved your original problem. Unless your own answer is the solution.

Hi - Sorry. Now marked your post.
Thanks

 

[DanteAmor]

I have two sets of data identified as 'Phase 1' and 'Phase 2'. Each identified with an 'x' in columns I & J.
I want to be able to run the macro for 'Phase 1' data, which has an 'x' in column I.
And then run it again for 'Phase 2' data, which has an 'x' in column J.

It seems to be a new requirement, the macro must be changed, create a new thread.