Aug 07 2022 04:14 PM - edited Aug 07 2022 10:21 PM
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 accounts, their orders, and the part numbers within each ordered. The data is structured at the part number level, as seen in the example below:
I want to see how many accounts are placing identical orders, meaning, the orders contain the same set of part numbers.
For example, in the image below, I have highlighted the rows pertaining to order O-R101248, placed by "Company 1". I want whichever formula/logic I use to scan the data set to see how many times an order placed by "Company 1", matches "O-R101248". Note, it would need to be a match at the company and Part Number Level. You can see O-R101248 and O-R104338 below are a match at the company and part number level.
I would want to be able to set up a formula that would scan every order in this data set and give me the following output:
Is there a formula or set of formula's I can use to perform this analysis? Is there also a way to spit out the order numbers that fall under each category of the orange table (unique orders and duplicate orders)? If not possible in Excel, is there any code based strategy I can use such as pandas/python?
Any help would be greatly appreciated.
Aug 07 2022 07:31 PM
Aug 07 2022 10:24 PM
Aug 08 2022 02:20 AM
To extract unique company use-
=UNIQUE(A4:A87)
To count Total orders in Data set
=COUNTIFS($A$4:$A$87,E11)
To Count of Orders with no identical match
=SUM(--(UNIQUE(FILTER($C$4:$C$87,$A$4:$A$87=E11))<>""))
To Count of orders with identical part numbers
=SUM(--(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,FILTER($C$4:$C$87,$A$4:$A$87=E11))&"</s></t>","//s[preceding::*=.]")<>""))
Check the attach file.
Aug 08 2022 03:16 AM
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)))
)