• 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
 
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

  • Same_Items.xlsx
    19.9 KB · Views: 3
Hi Bluesky63, please see attached, I enter the result I would like to have, but manually did
 

Attachments

  • Same_Items (1).xlsx
    21 KB · Views: 11
Joyce007
Which sheet is Your 'the result' which You would like to have?
... I added something same to Sheet1 as in Result-sheet
 

Attachments

  • Same_Items (1).xlsx
    25.5 KB · Views: 9
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

  • Chandoo43894Same_Items.xlsm
    116.1 KB · Views: 5
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

  • Same Items.xlsx
    12.9 KB · Views: 9
Last edited:
GraH - Guido
No worry, there seems to be differences left side
- but, what would be needed result?
Formulas - I won't look any formulas, if those are longer than ~'30'-marks.
 
Back
Top