Find most combinations of numbers.

[MPFX2022]

Greeting all,
I'm trying to find the most ordered combination of menu items.
The sample pic Ive uploaded shows the customer and what number from menu they ordered.
So customer 1 and 10 ordered the same items..
I want to find the top 5 combinations from over 2000 orders..
This table is just an example to make things easier.
Ive tried filters but cant find anything. Is there a countif formula that would work perhaps?
ANY help would be greatly appreciated

 

[BrerRabbit]

This requires a couple of things:, sum, large and match.

1. SUM - Add customer menu number in teh sum column G4=sum(D4:F4)
2. LARGE - To find the largest, J4 = LARGE($G$4:$G$13,1) - you need the $ here to fix the criteria. The 1 will change to a 2 and 3 and so forth for the second largest, third largest, etc.
3. MATCH - to find those who are in top three here, K4 = MATCH(J4,G4:G13,0). This formula will work the same if you put in a column for the name of the party, for example.

customer	Menu#	Menu#	Menu#	sum			LARGE	MATCH
1​	4​	9​	8​	21​		1st	39​	8​
2​	9​	1​	3​	13​		2nd	38​	3​
3​	4​	21​	1​	26​		3rd	26​	1​
4​	8​	9​	21​	38​
5​	3​	8​	7​	18​
6​	3​	4​	8​	15​
7​	4​	6​	2​	12​
8​	24​	12​	3​	39​
9​	8​	2​	6​	16​
10​	9​	8​	4​	21​

 

[MPFX2022]

This requires a couple of things:, sum, large and match.

1. SUM - Add customer menu number in teh sum column G4=sum(D4:F4)
2. LARGE - To find the largest, J4 = LARGE($G$4:$G$13,1) - you need the $ here to fix the criteria. The 1 will change to a 2 and 3 and so forth for the second largest, third largest, etc.
3. MATCH - to find those who are in top three here, K4 = MATCH(J4,G4:G13,0). This formula will work the same if you put in a column for the name of the party, for example.

customer	Menu#	Menu#	Menu#	sum			LARGE	MATCH
1​	4​	9​	8​	21​		1st	39​	8​
2​	9​	1​	3​	13​		2nd	38​	3​
3​	4​	21​	1​	26​		3rd	26​	1​
4​	8​	9​	21​	38​
5​	3​	8​	7​	18​
6​	3​	4​	8​	15​
7​	4​	6​	2​	12​
8​	24​	12​	3​	39​
9​	8​	2​	6​	16​
10​	9​	8​	4​	21​

Thanks heaps for reply, but this wont work for what I need. The thing is each number represents an item. eg 4 = Caesar salad, 21 = pancakes. So sum of numbers could be multi combinations. eg 5+10 + 6 = 21. and so does 8+12+1 etc etc... I had thought of your method.
What I need is to find how often the combo of individual numbers comes up.
So Customer 1 and Customer 10 both ordered the same thing. items 4, 8, 9. So this = twice I need to find how often each combo comes up. Hope this makes sense??

 

[BrerRabbit]

Thanks heaps for reply, but this wont work for what I need. The thing is each number represents an item. eg 4 = Caesar salad, 21 = pancakes. So sum of numbers could be multi combinations. eg 5+10 + 6 = 21. and so does 8+12+1 etc etc... I had thought of your method.
What I need is to find how often the combo of individual numbers comes up.
So Customer 1 and Customer 10 both ordered the same thing. items 4, 8, 9. So this = twice I need to find how often each combo comes up. Hope this makes sense??

Then you need a count, to treat the number like it's text.

3​

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

4​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

18​

19​

20​

21​

5​

2​

2​

3​

5​

0​

2​

0​

6​

5​

0​

0​

1​

0​

0​

0​

0​

0​

0​

0​

0​

3​

O5

 

[BrerRabbit]

Then you need a count, to treat the number like it's text.

3​

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

4​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

18​

19​

20​

21​

5​

2​

2​

3​

5​

0​

2​

0​

6​

5​

0​

0​

1​

0​

0​

0​

0​

0​

0​

0​

0​

3​

O5

Sorry didn't finish.

O5=COUNTIF($D$4:$F$13,O4)
Continue across teh row.

Then do your large formula

 

[kevin9999]

If you're open to a VBA solution, then please try the following on a copy of your workbook. Assumes your headers are in row 1 and your Customer column is column A.

Option Explicit
Sub Top5Choices()
    Dim LRow As Long
    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    With ActiveSheet
        With .Range(.Cells(2, 5), .Cells(LRow, 5))
            .Formula = "=TEXTJOIN(""|"",,SORT(B2:D2,,1,TRUE))"
            .Value = .Value
        End With
        With .Range(.Cells(2, 6), .Cells(LRow, 6))
            .Formula = "=COUNTIF(R2C5:R" & LRow & "C5,RC5)"
            .Value = .Value
        End With
        With .Range(.Cells(2, 5), .Cells(LRow, 6))
            .Sort key1:=Range("F2:F" & LRow), order1:=xlDescending, Header:=xlNo
            .RemoveDuplicates Columns:=1, Header:=xlNo
        End With
        .Range("E1:F1") = Array("Top 5", "Frequency")
        With .Range("F:F")
            .AutoFilter 1, 5, 3         '<-- *** note the "5" represents the top 5 - change to suit
        End With
    End With
End Sub

 

[MPFX2022]

Hi, Thanks again but its not what Im trying to do. Sorry if Im making it confusing.
So each customer places an order from menu. Customer 1 and 10 both order the same items 4,9,8. That count would be 2
Im trying to find out most popular combination of orders that each customer has ordered..
So order could be 1,2,3. or 9,10, 20 etc etc.
Like how often do the same set of numbers in each row occur or repeat when compared with all other rows going down sheet.
The amount of different possible combinations is crazy, then to match those combinations with matching combinations..

Sorry didn't finish.

O5=COUNTIF($D$4:$F$13,O4)
Continue across teh row.

Then do your large formula

Thanks but still not quite what Im after

 

[MPFX2022]

If you're open to a VBA solution, then please try the following on a copy of your workbook. Assumes your headers are in row 1 and your Customer column is column A.

Option Explicit
Sub Top5Choices()
    Dim LRow As Long
    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    With ActiveSheet
        With .Range(.Cells(2, 5), .Cells(LRow, 5))
            .Formula = "=TEXTJOIN(""|"",,SORT(B2:D2,,1,TRUE))"
            .Value = .Value
        End With
        With .Range(.Cells(2, 6), .Cells(LRow, 6))
            .Formula = "=COUNTIF(R2C5:R" & LRow & "C5,RC5)"
            .Value = .Value
        End With
        With .Range(.Cells(2, 5), .Cells(LRow, 6))
            .Sort key1:=Range("F2:F" & LRow), order1:=xlDescending, Header:=xlNo
            .RemoveDuplicates Columns:=1, Header:=xlNo
        End With
        .Range("E1:F1") = Array("Top 5", "Frequency")
        With .Range("F:F")
            .AutoFilter 1, 5, 3         '<-- *** note the "5" represents the top 5 - change to suit
        End With
    End With
End Sub

Thank you, will try..

 

[MPFX2022]

Thank you, will try..

Perfect.. thank you so much

 

[kevin9999]

Happy to help & thanks for the feedback