Excel 2010 formula to delete repeated content based on value

microsoft excelmicrosoft-excel-2010worksheet-function

I have a spreadsheet with two columns, a name and a value. The name is repeated several times with different values, for instance:

Name – 1
Name – 2
Name – 3

etc.

I am looking for a formula that will go through the spreadsheet and delete all duplicate instances of the name column, saving the one with the second highest value. So if I input a spreadsheet like the one above, it would save the row "Name – 2" and delete the others. Is this possible?

EDIT: The spreadsheet has 6000+ values, so I'd prefer a solution that's as automated as possible. I was thinking something like:

Sort values by name, then value.
Formula that deletes duplicate rows with lowest values.
Formula that deletes all rows but lowest values.

Best Answer

You can get what you want with a helper column and a filter.

Step 1: Helper column

Add a column to your table with the following formula, where the names are in A2:A18 and the values are in B2:B18.

=OR(COUNTIF($A$2:$A$18,A2)=1,SUMPRODUCT(1*(B2<$B$2:$B$18),1*(A2=$A$2:$A$18))=1)

This formula will return TRUE for the rows you want to keep, i.e., with the second highest value for duplicate names, and any non-duplicate names (like d in my example below). If by chance you DON'T want to keep non-duplicate rows, you can use the following formula instead.

=SUMPRODUCT(1*(B2<$B$2:$B$18),1*(A2=$A$2:$A$18))=1

helper column example

Step 2: Filter

Simply filter the entire table for rows that are TRUE in the helper column.

filter

If filtering is not enough, and you really need to delete the other data, you can just copy and paste the filtered result to another table, and then after deleting the original table, you can paste in your clean copy.

Even simpler

But if we consider that ISBLANK() function returns FALSE where there's a formula in a cell as well, we can simplify this even further by using this formula only:

=ISBLANK($D1)

Mac – excel macro – how to delete rows based on a cell value

Here it is:

Public Sub removeRows()
    Application.ScreenUpdating = False
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    filtercolumn = "G"
    Set wks = wkb.Sheets("Sheet1")
    totalrows = wks.Cells(Rows.Count, "A").End(xlUp).Row
    For j = totalrows To 1 Step -1
        If wks.Cells(j, filtercolumn) = 0 Then
            wks.Rows(j).Delete
        End If
    Next j
    Application.ScreenUpdating = True
    themessage = MsgBox("Finished", vbInformation)
End Sub

Open Macros with ALT + F11, inser a new module under ThisWorkbook, and paste the code on the right side.

Best Answer

Related Solutions

Excel conditional formatting based on a formula

Even simpler

Mac – excel macro – how to delete rows based on a cell value

Related Question