Delete duplicate rows while keeping the largest value and smallest value corresponding to the same name

[sofas]

Hello. I have a list of duplicate names in column B looking for a way that enables me to delete duplicate rows provided only the largest value and the smallest corresponding value of the same name are kept in column E in case the number of iterations of the name exceeds more than 2 times

TEST DELETE.xlsm

Shared with Dropbox

www.dropbox.com

 

[dermie_72]

Have you tried:
Cell I1

=UNIQUE(B1:B21)

Make sure you encompass the entire range
Then in Cell L2

=MAXIFS(E:E,B:B,I2)

Fill down

That worked when I tested it.

 

[sofas]

Have you tried:
Cell I1

=UNIQUE(B1:B21)

Make sure you encompass the entire range
Then in Cell L2

=MAXIFS(E:E,B:B,I2)

Fill down

That worked when I tested it.

Thank you, but I don't want to extract the results elsewhere. What is required is to use the VBA code to delete the rows that contain duplicate values, while keeping the smallest and largest value for each duplicate name on column b.

 

[dermie_72]

Start at the bottom and work your way to the top and check to see if the value in E equals the maxifs of Range B. If not, delete, else continue.
This is some very simplistic code that will work. You can adjust it to become more versatile to suit your needs.

Sub sofas()
Dim I As Integer
Dim A As Integer
Dim nme As String

For I = 21 To 2 Step -1
    nme = Range("B" & I).Value
    A = Application.WorksheetFunction.MaxIfs(Range("E:E"), Range("B:B"), nme)
    If Range("E" & I).Value < A Then
        Range("B" & I & ":E" & I).Delete
        Else
    End If
Next I
    
End Sub

 

[sofas]

Start at the bottom and work your way to the top and check to see if the value in E equals the maxifs of Range B. If not, delete, else continue.
This is some very simplistic code that will work. You can adjust it to become more versatile to suit your needs.

Sub sofas()
Dim I As Integer
Dim A As Integer
Dim nme As String

For I = 21 To 2 Step -1
    nme = Range("B" & I).Value
    A = Application.WorksheetFunction.MaxIfs(Range("E:E"), Range("B:B"), nme)
    If Range("E" & I).Value < A Then
        Range("B" & I & ":E" & I).Delete
        Else
    End If
Next I
   
End Sub

Thank you. The code has been modified to suit my request. I appreciate your effort