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:

rgautam858_1-1659935880091.png

 

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.

 

rgautam858_2-1659935901569.png

 

rgautam858_5-1659935940744.png

 

 

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:

rgautam858_4-1659935923764.png

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!

@rgautam858 

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.

@rgautam858 

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

image.png

where the Lambda function in defined by

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