Deleting duplicates based on row with largest amount of filled cells.

[Nagorimoya]

Hi,

I have a macro which adds data from an external rapport onto a local excel-file.
This invariably leads to duplicates (based on ID).

As the data is updated from both sides, I want to make the macro which imports the rapport also be able to remove duplicates based on which rows has the most amount of cells filled in.

In short, from the example. I am struggling to create a macro that deletes all duplicates of dr. Pepper and only leaves the row in which the most data-points has been filled in (ie. row 5).

I have tried possibly creating a function that counts the filled cells and marks the top one, but I can't really find a way to make it work.

Any help or hints are greatly appreciated

[beyond Excel]

Hello. You can try with:

Sub Macro25() 
Dim R% 
Application.ScreenUpdating = False 
With Sheets("Data") 
  With .Cells(1).CurrentRegion 
    R = .Columns.Count: .Cut .Cells(1, 2): .Cells(1,0) = "Tttt" 
  End With 
  With .Cells(1).CurrentRegion 
    .Columns(1) = "=CountA(" & .Cells(1, 2).Resize(, R).Address(0, 0) & ")" 
    .Columns(1) = .Columns(1).Value: .Cells(1) = "Tttt" 
    .Sort .Cells(1, 2), 1, key2:=.Cells(1), order2:=2, Header:=xlYes 
    .RemoveDuplicates Columns:=2, Header:=xlYes 
    .Columns(1).Delete xlToLeft 
  End With 
End With 
End Sub