Count Pairs

[Motilulla]

Using Excel 2010

Hello,

I have lottery numbers in columns D:H I want to count each pair listed in range K3:T4 in there column, example pair 1&2 in column K, pair 1&3 in column L, and so on…..

Need VBA help? Example workbook attached.

Regards,
Moti

[jomili]

Motilulla,
Can you explain more on what you're trying to do? For instance, you said "example pair 1&2 in column K, ". So are you wanting a count of every time a 01 and a 02 both appear in the same row in D:H? Or do 1 and 2 relate to n1 and n2? Your sample is confusing without a good explanation and example.

[Motilulla]

@jomili, thank you for your replay, true is I want to count of every time any pair, example pair 01 and a 05 appear in the same row in D:H in any position

Whether example 01 and 05 can be relate to n1 and n2, n1 and n3, n1 and n4 or n1 and n5 position does not matter it must be counted under 01 and 05 in the column N, hope this help.

Regards,
Moti

[MrShorty]

Are you required to use VBA? It looks like it should be a simple worksheet formula. Something like:

1) A COUNTIFS() function to determine if the value in row 3 occurs in this row of data. COUNTIFS($D6:$H6,K$3). A similar COUNTIFS() function for the value in row 4 COUNTIFS($D6:$H6,K$4). These will each return 1 if the value exists and 0 if it doesn't.
2) Add the two COUNTIFS() together. If this sum is 2, then both values existed. If it is 1 or 0, then both values do not exist.
3) Nest that inside of an IF() function to choose what to return =IF(COUNTIFS(...)+COUNTIFS(...)=2,1,""). As programmed, I see it being entered in K6. Note the mix of relative and absolute references and copy/paste/fill into K6:T305.

Does that help?

[Motilulla]

@MrShorty, formula is ideal it worked fine! Thank you

Regards,
Moti