filtering/sorting a deleting

New Contributor

Hi guys

I have a spreadsheet I'm trying to sort, I have a list of numbers (including duplicates) which either come back with a pass or fail on different dates.

If the newest date is a "fail" I want to keep that and delete the previous fails, And if it's a pass I want to delete the pass and all the previous fails relating to the specific number.

4 Replies

Can I say in this way:

Keep the latest record of each SN if it is fail. Otherwise, delete all records about that SN.


If not, could you please provide a sample file about what you want to accomplish?  Thank you.

Hi Willy


Thanks for the reply I have added Sample with text.


Im sure it's a simple fix but, but not to hot on the old "marcos" if that's even what would be required to solve my issue.




Thanks for the sample file.


May I know how many rows are in the original file roughly and Excel version?  I did it in array formula and it is slow.



Here is the variant with Power Query (aka Get&Transform). If I understood the logic correctly we remove all records for IDs who passed, and keep only latest FAILED record for ones who didn't pass.


The script could be like

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // Select only IDs who PASSED
    OnlyPassed = Table.SelectRows(Source, each ([Inspection Outcome] = "PASSED")),
    PassedID = Table.SelectColumns(OnlyPassed,{"Number"}),
    // Merge with source table keeping only IDs who FAILED
    SelectFailed= Table.NestedJoin(PassedID,{"Number"},Source,{"Number"},"Source",JoinKind.RightAnti),
    KeepTableOnly = Table.SelectColumns(SelectFailed,{"Source"}),
    ExpandTable = Table.ExpandTableColumn(KeepTableOnly, "Source",
      {"Works Recorded Date", "Number", "End", "Insp. Date/Time", "Inspection Outcome"},
      {"Works Recorded Date", "Number", "End", "Insp. Date/Time", "Inspection Outcome"}),
    // Now remove duplicated IDs keeping latest Inspection Date/Time
    SortForRemoving = Table.Sort(ExpandTable,{{"Number", Order.Ascending}, {"Insp. Date/Time", Order.Descending}}),
    Buffer = Table.Buffer(SortForRemoving), // need the buffer for proper duplicates removing
    RemoveDuplicatedID = Table.Distinct(Buffer, {"Number"}), // keep only latest FAILED
    // Change on proper data types before return, mainly for Date and Date/Time columns
    ProperType = Table.TransformColumnTypes(RemoveDuplicatedID,{
      {"Works Recorded Date", type date},
      {"End", type date},
      {"Insp. Date/Time", type datetime},
      {"Number", Int64.Type},
      {"Inspection Outcome", type text}

Exactly the same could be done from user interface only if use separate queries for IDs who passed and source one.


Result is in second sheet of the attached file.