Forum Discussion
Identical Order Analysis by Account (Is this possible in Excel?)
I have an analysis I am trying to perform but am unsure of what formulas to use, or if excel can even do this. I have a set of data containing ordering history from multiple accounts. The table lists...
I have made some progress but not as far as the required format.
= LET(
distinctOrders, UNIQUE(CHOOSECOLS(ReferenceTable,2)),
companyName, XLOOKUP(distinctOrders, Order_Name, Account_Name),
partsByOrder, MAP(distinctOrders, PartsOrderedλ),
repetitions, MAP(partsByOrder,LAMBDA(p, SUM(IF(partsByOrder=p,1)))),
UNIQUE(HSTACK(repetitions, companyName, partsByOrder))
)generates
where the Lambda function in defined by
PartsOrderedλ
= LAMBDA(order,
TEXTJOIN("|", , SORT(FILTER(Part_Number, Order_Name = order)))
)