 New Contributor

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

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

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.

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

Hello @Harun24HR, I have updated the post and attached a mock file. Thank you!

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

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.

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

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