Ignore my last, think i figured it out, the below has set it up with the actions column to be split in column d and the data split starting from column E

Public Sub Remove_Duplicates_and_Sort_Data()
With Range(Cells(3, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, 4).Resize(, Application.Count(Rows(1)))
.FormulaR1C1 = "=IF(COUNTIF(R3C1:RC1,RC1)>1,NA(),REPT(R2C&"""",SUMPRODUCT(--(R3C1:R502C1=RC1),--(R3C4:R502C4=R1C))))"
.Value = .Value
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
End Sub
Thanks

Gibbo