co-occurrence between many combinations

0

I have 300 rows, and 40 columns. the columns are different product categories (e.g., 1, 2, 3, ...), and the rows are different individuals. the data are either 1 or 0. 1 means that the individual bought that product, and 0 means that individual didn't buy that product. I need to create a table that shows all the product combinations (e.g., 1,1; 1,2; 1,3; 1,4; ....2,2; 2,3; .....), and how many individuals have purchased both products for each combination. Is there a macro that can achieve this?

Answer
Discuss

Discussion


my data look like this: 
document ID 1 2 3 4 5   Product 1 Product 2 Product 3 Product 4 Product 5 1 1 0 0 0 0 2 1 1 0 0 0 3 1 1 0 0 0 4 1 1 0 0 0 5 1 1 0 0 0 6 1 1 1 0 1 7 1 1 0 0 1 8 1 1 0 0 0 9 1 1 0 0 0 10 1 1 0 0 0
I hope to get a table like this: 
Product  Product total number of individuals who bought both products 1 2 68 1 3   1 4   1 5   1 6   1 7   1 8   1 9   1 10   2 3   2 4   2 5  
Can this be done in Pivot table? Many thanks!
Jake2018 Feb 2, '18 at 3:20 pm
Add to Discussion

Answers

0

A Pivot table may organize your data in the format that you are looking for.  It would be helpful to have your data (uploaded to this forum) and show what you would like the results to look like.

You may also want to use Data Filtering to HIDE the Products NOT purchased.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login