Data Relationship?

[Q-D]

Hi Guys

I would like to get a way to find out the relationships between the parts ordered

I have attached a sample data set.

Eventually I would like to get to a stage when I can type in a part number and it will bring up other part numbers that were ordered with that part number. Ideally the top ten rather than having a huge lists of hundreds of parts.

I.e The top selling part number in the list is 11511607, I can easily make a formula to bring back all of the order numbers this was ordered on but I don’t want the order numbers, I want the other part numbers on the order in which this part was ordered.

I am trying to get to which parts are commonly ordered together and thus should be stored close together in the warehouse to speed up the picking processes.

Any help or guidance on how I may going about achieving this would be greatly appreciated.

Thanks

Simon

[Palmetto]

Here is one approach:

This will need to be a multi-step process involving two uses of Advanced Filter and then a Pivot Table.

First Advanced Filter
Source Range: Output Sheets columns A:B (Order Number & Part Number)
Criteria: A Part Number

The above will extract all of the order numbers associated with the part number, which, in turn, provides the criteria for second AF.

Second AF
Source Range: same as above
Criteria: all order numbers extracted in first AF

This will return all of the part numbers associated with the above work orders, which themselves are associated with the part number from the first AF.

Pivot Table
Create this based on the data extracted in the second AF. Set the Part Number as the Row Field pull the part number field into Values section of the PT layout using the Count option.

The Forum is giving me errors during attempts to upload a sample file. Hopefully, the above explanation is sufficient for now.

Edit: Had to ZIP the file to get it to upload. Didn't have time to automate the Advanced Filters with VBA as Stanley has done, but you still need to create a Pivot Table in order to obtain the counts of each part number

[stanleydgromjr]

Q-D,

See the attached workbook "Data Filter AutoFilter - Q-D - SDG10.xls", with your raw data on worksheet "Output", and two additional worksheets "Orders", and "OtherPartNumbers".

There is a command button on worksheet "Output", "Get Others".

Click on the command button, and all the other "Order Numbers" (and data) that contain the same "Part Number" as the "Part Number" in cell G2, will be copied to worksheet "OtherPartNumbers", less the rows that may contain the "Part Number" in cell G2.

We have gone from 6,999 rows of data in worksheet "Output", to 566 rows of data in worksheet "OtherPartNumbers".

Detach/open the workbook and click on the command button on worksheet "Output", "Get Others".

[Q-D]

Fantastic!!

Thankyou both for your help, much more than I expected I was hoping for a direction not the actual outcome.

It does amaze me the skills some people have!

Thanks