• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate frequency of the order have same 2 items

Joyce007

New Member
Hi is anyone can help me on this :

will need to calculate the frequency of the order have 2 same items,
for example the item A001 and A002 both of them are ordered at same time on same order, result will show 2 (order A12345678 and order C45064838 )

Order#Item#
A12345678A001
A12345678A002
B23456790A003
B23456790A004
C45064838A001
C45064838A002
C45064838A003

the result table

# of order have same 2 itemA001A002
A001
2​
A002
2​

what's the formula to get 2 ?

thank you thank you
Joyce
 

bluesky63

Member
Hi Joyce, not very clear what you want to achieve as there is no desire report output, however I think Power query group by and Pivot and achieve most of the scenario.

please provide a more clear output format
 

Attachments

p45cal

Well-Known Member
At A11 on the attached workbook's sheet Result2 is a table.
It uses a user-defined function, to be found in Module1. It can be tweaked by enabling a line:
Code:
'CountDistinctBoth = Join(.keys, ", ")
by removing the leading apostrophe. This will list in each cell the order numbers where both items have been ordered.

I can't help thinking I've made this more complicated than it needs to be!

66495
 

Attachments

bosco_yip

Excel Ninja
1] Here is a long formula solution, the result is similar to p45cal's UDF

2] See attachment (I put the Input / Output table in the same sheet for easy checking purpose)

66506

So,

if you wanted to show the frequency of order have same 2 items only, you could use Conditional Formatting to mask the values less than 2 with the same color in font and cell background.

Regards
Bosco
 

Attachments

Last edited:
Top