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?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
co-occurrence between many combinations
0
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
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.