Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Identical Order Analysis by Account (Is this possible in Excel?)

Copper Contributor

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.



4 Replies
You desired output doesn't match with your current input. Can you explain how you get your current output from given sample data? Edit your post and attach a sample file with desired output.
Hello @Harun24HR, I have updated the post and attached a mock file. Thank you!


To extract unique company use-


To count Total orders in Data set


 To Count of Orders with no identical match


 To Count of orders with identical part numbers


 Check the attach file.


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))



where the Lambda function in defined by

= LAMBDA(order,
    TEXTJOIN("|", , SORT(FILTER(Part_Number, Order_Name = order)))