FInd most common variation of product combination of with order type and complete order details

[bbell2219]

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.

 

[bbell2219]

Source Area vc_ordtyp Device Code Source Location Label Sequence Pick Quantity Order Line Item Number Order Number
DK LSE1 WZ-WA01 DK01C2 DK01C2 1 0008 1001-301002-001 451657801
DK LSE1 WZ-WA01 DK04A2 DK04A2 1 0010 1001-501046-001 451657801
DK LSE1 WZ-WA01 DK02C3 DK02C3 1 0011 573307-002-00 451657801
DK LSE1 WZ-WA01 DK02A1 DK02A1 1 0006 100003059 451659448
DK LSE1 WZ-WA02 DK08A1 DK08A1 1 0006 100003060 451657801
DK SWP3 WZ-WA01 DK01C2 DK01C2 1 0008 1001-301002-001 451660624
DK SWP3 WZ-WA01 DK04A2 DK04A2 1 0010 1001-501046-001 451660624
DK SWP3 WZ-WA01 DK02C3 DK02C3 1 0011 573307-002-00 451660624
DK SWP3 WZ-WA01 DK02A1 DK02A1 1 0006 100003059 451662261
DK SWP3 WZ-WA01 DK04C3 DK04C3 1 0010 ATT112830806-10 451662261
DK SWP3 WZ-WA01 DK04C1 DK04C1 3 0011 ATTWAFERSEAL 451662261
DK SWP3 WZ-WA02 DK08C1 DK08C1 1 0001 ATT152340911 451662261
DK SWP3 WZ-WA02 DK05D5 DK05D5 1 0008 100003061 451666522
DK SWP3 WZ-WA03 DK09A2 DK09A2 1 0001 585285-001-00 451660553
UV SWP1 WZ-ZD1 ZD02A2 ZD02A2 3 0006 ATTWAFERSEAL 451663962
UV SWP1 WZ-ZD1 ZD03A2 ZD03A2 1 0001 100001111 451664872
UV SWP1 WZ-ZD1 ZD01A2 ZD01A2 1 0001 100000894 451664881
UV SWP1 WZ-ZD1 ZD03A1 ZD03A1 1 0005 1001-500200-003 451665115
UV SWP1 WZ-ZD2 ZD07C1 ZD07C1 1 0006 ATT123520853-7 451664759
UV SWP1 WZ-ZD2 ZD06C3 ZD06C3 1 0006 ATT150150901-2 451665218
UV SWP1 WZ-ZD3 ZD12C1 ZD12C1 1 0005 ATT112830806-10 451663962
UV SWP1 WZ-ZD3 ZD10C4 ZD10C4 1 0008 ATT142960896-2 451663962
UV SWP1 WZ-ZD4 ZD15A1 ZD15A1 1 0001 100000896 451664853
UV SWP1 WZ-ZD4 ZD13A2 ZD13A2 1 0006 1001-500201-001 451665115
UV SWP1 WZ-ZD5 ZD17C1 ZD17C1 1 0008 ATT142960895-3 451664748
UV SWP2 WZ-ZD2 ZD08C1 ZD08C1 1 0001 1001-301002-001 451655453
UV SWP2 WZ-ZD2 ZD08C3 ZD08C3 1 0005 573307-002-00 451655453
UV SWP2 WZ-ZD4 ZD16A2 ZD16A2 1 0005 ATT151470908-2 451665296
UV SWP4 WZ-ZD1 ZD01A1 ZD01A1 1 0006 100003059 451655584
UV SWP4 WZ-ZD1 ZD01C2 ZD01C2 1 0001 ATT152340912 451655584
UV SWP4 WZ-ZD1 ZD02A2 ZD02A2 3 0011 ATTWAFERSEAL 451655584
UV SWP4 WZ-ZD2 ZD08A2 ZD08A2 1 0006 100003060 451663135
UV SWP4 WZ-ZD3 ZD12C1 ZD12C1 1 0010 ATT112830806-10 451655584
UV SWP4 WZ-ZD3 ZD10A2 ZD10A2 1 0001 586680-003-00 451666585

 

[Cindy Ellis]

Hello, and welcome to Mr. Excel!
Thanks for posting a sample of your data...that's a good starting point to explore how this could be solved.
I have a few questions.
1) Based on your sample data, which columns are meaningful in your analysis? All columns? Or just some of them?
2) Do parent-child relationships go left to right? In other words, given UV in column A, and SWP4 in column 2, is UV the parent of SWP4?
4) Given the sample data, what kind of output are you looking for? Are you just looking for frequent pairs, regardless of column?
Hoping to help,

 

[bbell2219]

Thank you for the reply and my apologies for not getting back sooner. The piece sample provided is the first piece which is overall order not the detailed breakdown containing all lines per order.

(1) In regards to your question of which columns for this dataset are meaningful? All of the data provided in that sample has an influence in the results of the analysis. The table it was pulled from originally contains over 200 columns of data, these columns provided are what I have determined has direct impact. The only one that doesn't play a factor in the current dataset is order number, but I included that in the dataset for reference in the order details table that breaks down every order piece by piece, line by line (to be provided in another comment).

(2) a. With the question of parent child. It doesn't necessarily go from left to right. I will try and explain as best as possible. UV and DK are my different picking areas, I decide what goes there by what product I place there. (LSE1, SWP3, SWP1, SWP2, COL, etc) and Item Number (100000894, 100000896, 100000896, etc) are the constant never changing data. Everything else is a result of Item placement which ultimately is my overall goal. Optimize product placement to increase production and efficiency.

(2) b. The vc_ordtyp (LSE1, SWP1, etc) then is broken down by Item Number which is tricky because the parent item number say for example 100000894 could be in three different Order types due to the different combinations of items that go with it. Which then brings up the argument that Item could be the parent because its in multiple family hierarchy's. However, there could be multiple different LSE1 orders, Several different SWP3, Several SWP4, and so on. Example LSE1 and SWP3 both contain almost exactly the same items except one extra item (573307-002-00) on an LSE1 order changes the paperwork that goes with it.

SWP4 (100003059, ATT152340912, ATT91800644-10, ATT132690873, ATT112830806-10, IPV6STICKER, ATTWAFERSEAL, 1001-500201-001, and1001-500200-003)

LSE1 (100003059, 573307-002-00,ATT151470907-3,1001-500201-001,1001-500200-003,1001-301002-001,1001-301003-001, and 1001-501046-001)

(3) In regards to what kind of output you looking for? Are you just looking a couple things. One being what item triggers the change like the example above showing that the difference of 573307-002-00 being added directly changed overall look of the order itself. The first result is a breakdown by item of what all order types it touches, what other items it touches, and what items it never touches if any.

I should have posted the detailed breakdown of every single item. I will do that with the next two comments. First will be an overall listing of every item, the second will be the breakdown in order of how I view importance. Thank you very much for your help.

 

[bbell2219]

Sample Order Details


ordtyp DEVCD SCLoc LBLSEQ Item Number QTY Line Area OrderNum
SWP2 WZ-ZD2 ZD08C1 ZD08C1 1001-301002-001 1 0001 UV 451655453
SWP2 WZ-ZD2 ZD05A2 ZD05A2 1001-301003-001 3 0003 UV 451655453
SWP2 WZ-ZD2 ZD11A1 ZD11A1 1001-501046-001 1 0004 UV 451655453
SWP2 WZ-ZD2 ZD08C3 ZD08C3 573307-002-00 1 0005 UV 451655453
SWP2 WZ-ZD2 ZD11C1 ZD11C1 K2UV3387COL 1 0006 UV 451655453
SWP2 WZ-ZD2 ZD06A1 ZD06A1 ATT151470907-2 1 0007 UV 451655453
SWP1 WZ-ZD2 ZD13C2 ZD13C2 ATT101500710-3 1 0002 UV 451665625
SWP1 WZ-ZD2 ZD12C1 ZD12C1 ATT112830806-10 1 0004 UV 451665625
SWP1 WZ-ZD2 ZD02A2 ZD02A2 ATTWAFERSEAL 3 0005 UV 451665625
LSE1 WZ-WA01 DK04A1 DK04A1 K2UV3387COL 1 0001 DK 451657801
LSE1 WZ-WA01 DK09C1 DK09C1 ATT151470907-3 1 0003 DK 451657801
LSE1 WZ-WA01 DK09A2 DK09A2 585285-001-00 1 0004 DK 451657801
LSE1 WZ-WA01 DK08A1 DK08A1 100003060 1 0006 DK 451657801
LSE1 WZ-WA01 DK05C1 DK05C1 579761-017-00 1 0007 DK 451657801
LSE1 WZ-WA01 DK01C2 DK01C2 1001-301002-001 1 0008 DK 451657801
LSE1 WZ-WA01 DK02A2 DK02A2 1001-301003-001 3 0009 DK 451657801
LSE1 WZ-WA01 DK04A2 DK04A2 1001-501046-001 1 0010 DK 451657801
LSE1 WZ-WA01 DK02C3 DK02C3 573307-002-00 1 0011 DK 451657801
SWP4 WZ-ZD1 ZD01C2 ZD01C2 ATT152340912 1 0001 UV 451655584
SWP4 WZ-ZD1 ZD12C5 ZD12C5 ATT91800644-10 1 0003 UV 451655584
SWP4 WZ-ZD1 ZD13A2 ZD13A2 1001-500201-001 1 0004 UV 451655584
SWP4 WZ-ZD1 ZD01A1 ZD01A1 100003059 1 0006 UV 451655584
SWP3 WZ-WA03 DK09A2 DK09A2 585285-001-00 1 0001 DK 451660553
SWP3 WZ-WA03 DK08A1 DK08A1 100003060 1 0003 DK 451660553
SWP3 WZ-WA03 DK05C1 DK05C1 579761-017-00 1 0004 DK 451660553
SWP3 WZ-WA03 DK01C4 DK01C4 K2UV30AVCOL2 1 0005 DK 451660553

 

[bbell2219]

100000894,100003059,1001-501046-001,att121140829-4,att143380897,k2uv884two
100000896,100003060,579761-017-00,att122600849-4,att150150901-2,k2uv3389col
100001089,100003157,585285-001-00,att123520853-7,att150150902,k2uv30avcol2
100001111,100003267,586680-003-00,att130300854-3,att151470907-2,k2uv3387col,
100002627,573307-002-00,att101500710-3,att130320862-4,att151470907-3,cy-rc3033-at,
100002628,1001-301002-001,att110900769-10,att130950864-3,att151470908-2,attwaferseal
100002777,1001-301003-001,att110900769-9,att132690873,att152340911
100002917,1001-500200-003,att112830806-10,att142960895-3,att152340912
100002977,1001-500201-001,att121140818-3,att142960896-2,att72300517-12
ipv6sticker,cy-rc1057-at,att91800644-10,k2uv3036col2,k2uv3388col