filtering/sorting a deleting

Copper 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.

 

Cheers

Andy 

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

let
    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}
   })
in
    ProperType

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.