Keep only row with lowest value for each ID-CodePudding

For each ID find the value in column C and delete other rows.

I want solution only because I want to buildup on this code to add more criteria.

Before

After

I have tried coding but I am not able to get it working where it does it for each ID. It is only keeping 1 row for entire sheet.

Sub Celltest()
    For Each rw In Sheets("Sheet1").Range("A:C").Rows
        For Each cel In rw.Cells
            For Each char In cel.Characters
                If char.Font.Underline = True Then MsgBox char
            Next
        Next
    Next
End Sub

CodePudding user response：

With VBA:

Option Explicit

Sub FilterUniqueLowestValue()

    Dim RG As Range
    Dim lRow As Long
    
    lRow = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
    Set RG = Sheet3.Range("A2:C" & lRow)
    
    With RG
        .Sort _
            Key1:=RG(3), _
            Order1:=xlAscending, _
            Header:=xlYes
        .RemoveDuplicates _
            Columns:=1, _
            Header:=xlYes
    End With
    
End Sub

<-- OR -->

With Formulas:

CodePudding user response：

In addition to by formula and vba, this can also be accomplished using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac). It should be pretty easy to add more criteria.

To use Power Query

Select some cell in your Data Table
Data => Get&Transform => from Table/Range
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table33"]}[Content],

//Set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"ID", Int64.Type}, {"Name", type text}, {"Value", type number}}),

//Group by ID and Name
//Then extract the minimum value for each subgroup
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Name"}, {
        {"Value", each List.Min([Value]), type nullable number}
        })
in
    #"Grouped Rows"

Source

Results