I have a data set containing sales orders made of various combinations of different parts.
What I want to do is find the connections between different parts - which parts are most often sold together.
What I need is a way to count how many times Part1 has the same Sales order number as Part2 and Part3 and so on.
I just don't have a clue on how to perform this operation. I have included an example of my data and how I imagine the results could be presented.
Any ideas?
Edit: I use Excel 2010.
Data:
<TBODY>
</TBODY>
Desired result:
<TBODY>
</TBODY>
What I want to do is find the connections between different parts - which parts are most often sold together.
What I need is a way to count how many times Part1 has the same Sales order number as Part2 and Part3 and so on.
I just don't have a clue on how to perform this operation. I have included an example of my data and how I imagine the results could be presented.
Any ideas?
Edit: I use Excel 2010.
Data:
Salesorder</SPAN> | Partnumber</SPAN> |
SO-001</SPAN> | PN-001</SPAN> |
SO-001</SPAN> | PN-002</SPAN> |
SO-001</SPAN> | PN-003</SPAN> |
SO-002</SPAN> | PN-004</SPAN> |
SO-002</SPAN> | PN-005</SPAN> |
SO-002</SPAN> | PN-006</SPAN> |
SO-002</SPAN> | PN-002</SPAN> |
SO-003</SPAN> | PN-002</SPAN> |
SO-004</SPAN> | PN-002</SPAN> |
SO-004</SPAN> | PN-007</SPAN> |
SO-004</SPAN> | PN-008</SPAN> |
SO-005</SPAN> | PN-001</SPAN> |
SO-005</SPAN> | PN-002</SPAN> |
SO-006</SPAN> | PN-002</SPAN> |
SO-006</SPAN> | PN-009</SPAN> |
SO-007</SPAN> | PN-010</SPAN> |
SO-007</SPAN> | PN-002</SPAN> |
SO-007</SPAN> | PN-009</SPAN> |
SO-007</SPAN> | PN-006</SPAN> |
SO-008</SPAN> | PN-005</SPAN> |
SO-008</SPAN> | PN-002</SPAN> |
SO-008</SPAN> | PN-007</SPAN> |
SO-009</SPAN> | PN-009</SPAN> |
SO-009</SPAN> | PN-002</SPAN> |
SO-009</SPAN> | PN-006</SPAN> |
SO-010</SPAN> | PN-002</SPAN> |
SO-011</SPAN> | PN-006</SPAN> |
SO-011</SPAN> | PN-002</SPAN> |
SO-012</SPAN> | PN-007</SPAN> |
SO-012</SPAN> | PN-002</SPAN> |
SO-013</SPAN> | PN-009</SPAN> |
SO-013</SPAN> | PN-002</SPAN> |
SO-014</SPAN> | PN-001</SPAN> |
SO-014</SPAN> | PN-002</SPAN> |
SO-015</SPAN> | PN-007</SPAN> |
SO-015</SPAN> | PN-002</SPAN> |
SO-015</SPAN> | PN-003</SPAN> |
SO-016</SPAN> | PN-008</SPAN> |
SO-016</SPAN> | PN-002</SPAN> |
SO-017</SPAN> | PN-002</SPAN> |
SO-018</SPAN> | PN-008</SPAN> |
SO-018</SPAN> | PN-002</SPAN> |
SO-018</SPAN> | PN-009</SPAN> |
SO-018</SPAN> | PN-010</SPAN> |
SO-019</SPAN> | PN-009</SPAN> |
SO-019</SPAN> | PN-002</SPAN> |
SO-019</SPAN> | PN-006</SPAN> |
SO-020</SPAN> | PN-009</SPAN> |
SO-020</SPAN> | PN-006</SPAN> |
SO-020</SPAN> | PN-002</SPAN> |
SO-021</SPAN> | PN-002</SPAN> |
SO-021</SPAN> | PN-009</SPAN> |
SO-022</SPAN> | PN-006</SPAN> |
SO-022</SPAN> | PN-002</SPAN> |
SO-022</SPAN> | PN-009</SPAN> |
SO-023</SPAN> | PN-007</SPAN> |
SO-023</SPAN> | PN-002</SPAN> |
SO-024</SPAN> | PN-002</SPAN> |
<TBODY>
</TBODY>
Desired result:
PN-001</SPAN> | PN-002</SPAN> | PN-003</SPAN> | PN-004</SPAN> | PN-005</SPAN> | PN-006</SPAN> | PN-007</SPAN> | PN-008</SPAN> | PN-009</SPAN> | PN-010</SPAN> | |
PN-001</SPAN> | X</SPAN> | 3</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
PN-002</SPAN> | 3</SPAN> | X</SPAN> | 2</SPAN> | 1</SPAN> | 2</SPAN> | 6</SPAN> | 5</SPAN> | 3</SPAN> | 7</SPAN> | 2</SPAN> |
PN-003</SPAN> | 1</SPAN> | 2</SPAN> | X</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
PN-004</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | X</SPAN> | 1</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
PN-005</SPAN> | 0</SPAN> | 2</SPAN> | 0</SPAN> | 1</SPAN> | X</SPAN> | 1</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
PN-006</SPAN> | 0</SPAN> | 6</SPAN> | 0</SPAN> | 1</SPAN> | 1</SPAN> | X</SPAN> | 0</SPAN> | 0</SPAN> | 5</SPAN> | 1</SPAN> |
PN-007</SPAN> | 0</SPAN> | 5</SPAN> | 1</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | X</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> |
PN-008</SPAN> | 0</SPAN> | 3</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | X</SPAN> | 1</SPAN> | 1</SPAN> |
PN-009</SPAN> | 0</SPAN> | 7</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 5</SPAN> | 0</SPAN> | 1</SPAN> | X</SPAN> | 2</SPAN> |
PN-010</SPAN> | 0</SPAN> | 2</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | 1</SPAN> | 2</SPAN> | X</SPAN> |
<TBODY>
</TBODY>
Last edited: