Find most common variation of product combination of with order type and order details

I am trying to use a sample size worth of orders to create a relationship in regards to parent-child relationships and most common occurrences of each relationship. I have tried to use several different variations of if statements along with MODE.MULT, VLOOK, TRANSPOSE, SUMPRODUCT to determine the multiple different occurrences of different product/items and order types to different areas with no results.

The first idea is to determine the rank and list the results with all of the details, then use that information to input into a forecast that says I will do this over 6 months. I then want to change certain layouts of where the product is to make the area more efficient. I have a forecast model that I will use that resulting data over a 3 to 6 month period to determine probability for the next quarter. On the order
details I would like to see how every main component (parent) relates to one another (children) and ultimately design a warehouse picking aisle that has as little movement as possible. Example Part # 573307-002-00 goes to a swp2 order and an lse1 order which the main components could be brought closer together to reduce walking time for both orders . I have the slotting piece and forecast model complete already figured out. Its this piece of the puzzle I am missing. Can anyone assist? Below are screen shots of what the data looks like.
[attach] [/attach]

let me start by saying that's a good amount of data to analyze I may not find the best solution for you but I'm interested to look at it. first question in the workbook "order info" sheet "item Usage" it appears that most of the items are listed more then once, would you like to consolidate all this just by the item number for better totalling?

Yes please, I forgot to use the sumif function prior to my saving the worksheet and uploading. You could say I am looking for a family hierarchy of sorts. Using the Hierarchy to determine what item is constant with the most down to the least most common relationship. Then use that to create a hierarchy based on Type.

I wouldn't say what item is consistant with the most, id look at what type is most, then place all the common items off that type closest together after that take the remaning items off the most popular type next. its hard to say what item is purhacsed the most because if your picking for 1 order and there happens to be 3 or 4 of the same item in one order then it will skew the results as there is no travel time with the same item under the same order #

Correct, that is why I am having a hard time determining what is what here. How would you determine that?

Analytics and excel, lol. I'll play around with it a bit tomorrow it's been a long week. enough that I don't want to start writing formulas and code tonight

That's a great reply. I honestly don't have time to write the algorithms and code anytime soon. Mrshorty gave you an awesome response and a great starting place. Who set up the spread sheet originally. Maybe they could add some insight to you?