finding recurrent patterns

[Kschott]

Hi there,
I'm trying to find a way to analyze a set of data to find recurrent patterns.
I provide an example, where I want to see if team leads are working together frequently. In the given example I want to find that Ellen, Thomas and Joey frequently work together.
Kind regards,
Kim example recurring boxes.xlsx

[Sintek]

One way I guess...

Please Login or Register  to view this content.

[Kschott]

sorry, I didn't explain clearly. I want to know if there is a functionality that gives me the outcome that Ellen, Thomas and Joey frequently work together. I made the example so i know these 3 names, but in reality I do not know who works together.
K

[Sintek]

So what's the pattern frequency?

[Kschott]

That's what I need to find out, who works together and in what frequency. The real table has 26.000 entries.

[Sintek]

If you are able to supply your expected output result for a small data set and in your expected format then I am very certain that someone will be able to supply a solution...

[MrShorty]

I want to know if there is a functionality that gives me the outcome that Ellen, Thomas and Joey frequently work together.

If I think of the people (Ellen, Thomas, and Joey) as "products in a store" (they will probably get offended at the possibility, so I won't tell them) and the "boxes" as "transactions" (the boxes probably won't like me, either), then this looks an awful lot like market basket analysis.

IMO, by far the easiest way to do MBA is to get a preprogrammed utility. To my knowledge, Excel does not come with a pre-programmed utility (there was a brief time as part of the 2013 version, I think, where it did). If you are allowed to use utilities from outside Excel, Power Query might have an MBA utility, Power BI likely does (see tutorial here: https://blog.finance-bi.com/power-bi-basket-analysis/ ), Frontline's XLMiner (Frontline provides Excel's Solver utility) can do MBA (tutorial here: https://www.data-mania.com/blog/mark...ysis-in-excel/ ).

If you decide you are limited to native Excel tools, this guy appears to walk you through an approach that uses native Excel tools (I didn't sit through the whole video, so could be mistaken: https://www.youtube.com/watch?v=aslTl6i-hpQ ).

MBA is an old programming problem, so the easiest solution is to find someone else's solution and use it. If you are required to build your own utility, at least look at how others have solved the problem so you can understand the algorithms behind the problem.

How would you like to proceed? How can we help?

[josephteh]

The is what we I called "Also Buy". Customers who bought a certain product, what other products they also bought at the same time.

My way of doing it is a long way and only works on one selected product (team lead in this case) at a time, not much of a matrix.

Hopefully you guys can improve on it.

I created 3 helper columns C, D and E. Column C finds the invoices (boxes) with the selected product (team leader), column D ticks off all the invoices (boxes) of the selected products (team lead) and column E finds all the products (team lead) of these invoices (boxes)

Then it is a matter of using COUNTIF to find how many times the selected product (team lead) have been purchased (appeared) during the period and how many times other products (team leads) are purchased (appeared) together.

Note: invoice = box, product = team lead

[karedog]

Not too sure why it looked so difficult, this is just generating combinations and checking whether each combination occurred in a group.

Please Login or Register  to view this content.

[CheeseSandwich]

Maybe setting it out like a matrix will make spotting patterns easier on the eye:

Please Login or Register  to view this content.