Most frequent items bought together

Hi All,

I have a database of sales transactions and products bought within those transactions. A transaction could contain between 1 and 20 products. I am trying to find out what the most frequently bought items are, and groups of items.

This kind of analysis is called market basket analysis and I am trying to reconstruct this in excel.

I have attached a sample spreadsheet of the database and what I would like the output to be. The database could contain around 10,000 products within 2,000 transactions so would need a macro to be most efficient.

Hope someone can assist.

Many thanks

Mike

you can take the cells(r2,10) and cells(r2,11) entries I added them as checkups

Sub basket()

On Error Resume Next

r = 3
tr = Cells(2, 1)
Item = Cells(2, 2)
r2 = 2
r3 = 3
While Cells(r, 1) <> ""
  If Cells(r, 1) <> tr Then
    o = 1
    k = 1
    If Len(Item) > 1 Then
      While o = 1
        For i = 1 To Len(Item)
          For j = i + k To Len(Item)
            
            entry = Mid(Item, i, k) & Mid(Item, j, 1)
            Cells(r2, 10) = tr
            Cells(r2, 11) = entry
            r2 = r2 + 1
            x = 0
            x = Application.WorksheetFunction.Match(entry, Range("g:g"), 0)
            If x = 0 Then
              x = r3
              Cells(x, 5) = entry
              r3 = r3 + 1
            End If
           
            Cells(x, 6) = Cells(x, 6) + 1
          Next j
        Next i
        If k > Len(Item) - 1 Then o = 0
        k = k + 1
      Wend
    End If
    Item = ""
    tr = Cells(r, 1)
  End If
  Item = Item + Cells(r, 2)
  r = r + 1
Wend
 o = 1
    k = 1
    If Len(Item) > 1 Then
      While o = 1
        For i = 1 To Len(Item)
          For j = i + k To Len(Item)
            entry = Mid(Item, i, k) & Mid(Item, j, 1)
            Cells(r2, 10) = tr
            Cells(r2, 11) = entry
            r2 = r2 + 1
            x = 0
            x = Application.WorksheetFunction.Match(entry, Range("g:g"), 0)
            If x = 0 Then
              x = r3
              Cells(x, 5) = entry
              r3 = r3 + 1
            End If
            Cells(x, 6) = Cells(x, 6) + 1
          Next j
        Next i
        If k > Len(Item) - 1 Then o = 0
        k = k + 1
      Wend
    End If
End Sub

Hi

Thanks for the code.

How can this code be adjusted if the items had longer names and weren't single letters i.e the item name could be "apple ipad 64gb" for example.

Thanks

Mike

I thought you might ask that .. please send a couple of sample item codes and I'll adjust the code accordingly.

Originally Posted by MrShorty

A quick internet search for "market basket analysis" found this page (with sample spreadsheet) that may be instructive: http://people.revoledu.com/kardi/tut...sket/index.htm

Mr Shorty

Thanks for sharing the link. I was going throught the tutorial and wondered how can I create the 2D matrix that is being talked about here. I am sure that there is a standard way to create such an array in excel. Would somebody mind sharing the approach with me.

For reference I am attaching the excel sheet which has the illustration of what I want.

Thanks!

try this new code

Hi Mr Shorty,

Thanks. Already downloaded that spreadsheet but didn't think it was the right method to use for the vast amount of data I am working with.

Regards

Mike

Dear Mike:

The process does not know how many times an specific combination will have until the end. Let me think about how to exclude combinations larger than 6 or if they have only hit.

Ok thankyou. I appreciate you looking at it for me

Mike