excelexcel-formulauniqueminimumgroup

Excel Formula: Filter Group based on Smallest Values

Excel Formula

With the use of an Excel (Not VBA) array formula or similar preferably, due to the size and amount of data, I need to reduce the least amount of resources necessary to optimize performance due to the size (Over 200,000 records per each Column).

I have a set of data in columns A (helper: rank), B (country), C (order) ,and D (name), I would like to filter group based on Unique "Name" and their lowest matching value (min) "Order", to return only Country, Order and Name by group with minimum/smallest value.

With the use of a Helper Column A (Rank), I am able to achieve my desired results, by filtering B2:D13 when column A (rank) equals the value of "1". Unfortunately, it requires a great amount of CPU, Memory and Threads, thus taking much longer to process the data. See below the following Formulas and example of current output results.

Is there a way to nest or combine Column A (Rank) within Column F (Results) UNIQUE(FORMULA function?

Rank by Group FORMULA (A): =SUMPRODUCT((D2=$D$2:$D$13)*(C2>$C$2:$C$13))+1
Desired Output FORMULA (F): =UNIQUE(FILTER(B2:D13,A2:A13=1)

enter image description here

Display all values in range (B2:C13) in ascending order by group name and then by Order (1-10).

enter image description here

Solution

  • Here is two ways using Excel Formulas that works with MS365 and using Power Query I will be preferably using the latter one.

    enter image description here

    • Formula used in cell F1

    =LET(a, D2:D13, b, C2:C13, VSTACK(B1:D1,FILTER(B2:D13,MINIFS(b,a,a)=b)))

    Or Using Power Query add the following in the advanced editor of a blank query, after converting the ranges to structured references aka tables:

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupBy = Table.Group(Source, {"Name"}, {{"Order_Min", each List.Min([Order]), type number}, {"All", each _, type table [Country=text, Order=number, Name=text]}}),
    Expand = Table.ExpandTableColumn(GroupBy, "All", {"Country", "Order"}, {"Country", "Order"}),
    Conditions = Table.AddColumn(Expand, "Custom", each if [Order_Min] = [Order] then 1 else null),
    FilterRows = Table.SelectRows(Conditions, each [Custom] = 1),
    RemoveCols = Table.SelectColumns(FilterRows,{"Country", "Order_Min", "Name"})
in
    RemoveCols

    Update as proposed by OP: Variable a gives sorted array. based on that can accomplish the prior output as well.

    =LET(
     a, SORT(B2:D13,{3,2}),
     b, DROP(a,,2),
     c, UNIQUE(b),
     FILTER(a,1-ISNA(XMATCH(INDEX(a,,2),
     MAP(c,LAMBDA(x,MIN(FILTER(INDEX(a,,2),x=b))))))))