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)
Display all values in range (B2:C13) in ascending order by group name and then by Order (1-10).
Here is two ways using Excel Formulas that works with MS365
and using Power Query
I will be preferably using the latter one.
• 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:
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))))))))