Hello,

I’m a bit stuck on this silly problem, and if any of you can offer me some advice that’d be brilliant.

I have a set of data that shows the names of clubs a list of individuals are members of. The data lists the names of the individuals in column A and the names of the clubs they are members of in Column B, with a different cell in the column used for each club.

I want to find out which of the individuals are friends, and I can do this by working out the number of clubs that two separate individuals attend together. I’m struggling to pull this out in a Pivot Table/Chart though. I thought I could do it by having all the names along the x and y axis for a table, then the number of clubs they both attend in the middle. But I'm not sure how to do this. Any idea how I can approach it?

Thanks very much,

Ben