# 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# A12345678 A001 A12345678 A002 B23456790 A003 B23456790 A004 C45064838 A001 C45064838 A002 C45064838 A003

the result table

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

what's the formula to get 2 ?

thank you thank you
Joyce

#### Joyce007

##### New Member
I am think of to distinct count # order from the combination of 2 items (row and column in result table)

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

• 19.9 KB Views: 3

#### Joyce007

##### New Member
Hi Bluesky63, please see attached, I enter the result I would like to have, but manually did

#### Attachments

• 21 KB Views: 11

#### vletm

##### Excel Ninja
Joyce007
Which sheet is Your 'the result' which You would like to have?
... I added something same to Sheet1 as in Result-sheet

#### Attachments

• 25.5 KB Views: 8

#### 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!

#### Attachments

• 116.1 KB Views: 4

#### 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)

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

• 12.9 KB Views: 8
Last edited:

#### GraH - Guido

##### Well-Known Member
That's a crazy formula @bosco_yip. You can't wait to have LET ( ) available I reckon. Would make this way shorter.
Stay safe...

#### vletm

##### Excel Ninja
GraH - Guido
... with Pivot-table ( reply #6) can get same result as (reply #8) or what?
.. but Joyce007 ... would someday let us know - what really wants?

#### p45cal

##### Well-Known Member
.. with Pivot-table ( reply #6) can get same result as (reply #8) or what?
Not the same.

#### GraH - Guido

##### Well-Known Member
Hi @vletm, I actually did not look at the assignment that closely (as it seems to be answered). Was just stunned to see the formula.

#### vletm

##### Excel Ninja
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.