analyzing ticket purchaser correlations

sarahw2271

New member
Joined
Aug 20, 2014
Messages
8
Reaction score
0
Points
0
I have a spreadsheet containing the names and email addresses of everyone who has ever bought a ticket to one of our events (5 years worth of concerts). I want to know if there is a way to calculate how many people who bought tickets to a show also bought tickets to each of the other shows. I am trying to determine which concerts have the most overlap in audience. Are there any tricks in excel for doing this? (column A= event name, column B= user's email address)
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
This is called Basket Analysis. I answered a question about it on this forum some time back, at http://www.excelguru.ca/forums/show...DE-function-on-text&highlight=basket+analysis

That thread has a file attached that you might want to check out, although it's pretty complicated stuff, and very hard to do with formulas alone as I have done in that file. How many different concerts do you have in total? If the number is large, you'll probably need some dedicated software to do this kind of analysis for you. Such as SQL Server: see http://msdn.microsoft.com/en-us/library/dn282369.aspx where the article discusses an addin for SQL Server that you can use in Excel.
 
Last edited:

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
I found a sample file I put together demonstrating how to do something like this. But it's limited to finding the two most common items. View attachment Basket analysis 8.xlsb

You could use VBA to do more, but note that this is a very computationally intensive thing indeed. Excel will quickly run out of resources if the lists involved are long.
 
Top