matches

[Ray Newman via OfficeKB.com]

I have a infomation base of numbers in a spreadsheet that I am trying to
get the number of matches from. I am trying to display the number of
matches for 2 numbers, 3 numbers, 4 number.....10 numbers


A B C D E F G H I J K L M N O P Q R S T
1 8 10 13 16 19 22 28 32 33 37 41 46 47 50 51 52 53 55 71 76
2 6 11 16 19 20 29 31 35 39 44 50 57 58 59 60 62 71 72 76 77
3 3 5 6 8 9 10 11 16 17 28 31 41 42 43 45 46 56 61 69 79
4 5 7 12 13 15 16 17 19 21 25 41 45 47 48 53 58 63 69 76 79
5 1 2 4 7 8 12 17 19 25 27 29 34 38 44 47 60 62 64 73 75
6 1 4 6 7 8 9 11 13 14 19 25 26 37 39 41 60 61 62 63 80

The display i am searching for will give me results like:

combo times
8 10 2
25 47 2
13 16 19 2
showing all combos up to a 10 number max

any help would be greatly appreciated
thanks, Ray

 

[Max]

One play to tinker with ..

Assuming the info base is in Sheet1, in A1:T6

In Sheet2
--------
Let's reserve cells A1:J1 for the input of the numbers (combos)
to be checked against the info base in Sheet1
(from 1 number to 10 numbers)

Put in A2: =IF(A$1="","",MATCH(A$1,Sheet1!$A1:$T1,0))
Copy A2 across to J2, fill down to say, J100
(to cater up to 99 rows of results in Sheet1)

Put in say, K1:

=IF(COUNTBLANK(A1:J1)=10,"",SUMPRODUCT(--ISNUMBER((IF(A1<>"",A2:A100)*(IF(B1
<>"",B2:B100)*(IF(C1<>"",C2:C100)*(IF(D1<>"",D2100)*(IF(E1<>"",E2:E100)*(I
F(F1<>"",F2:F100)*(IF(G1<>"",G2:G100)*(IF(H1<>"",H2:H100)*(IF(I1<>"",I2:I100
)*(IF(J1<>"",J2:J100))))))))))))))

K1 will return the desired count,
i.e. the number of rows in Sheet1's info base
which house/contain *all* the numbers entered into A1:J1
(Inputs can be made anywhere in A1:J1, in any order,
from 1 number up to a max of 10 numbers)

Try inputting the test combos below anywhere in A1:J1 in any order

combo times
8 10 2
25 47 2
13 16 19 2

The results will return as indicated in K1

 

[Ray Newman via OfficeKB.com]

max...

I get false in all cells a2 thru j20

also i get a invalid error for the k1 formula

is it possible for a formula to give me the number combinations to look up
also?

thanks Ray

 

[Max]

Some clarifications to your comments ..

.... I get false in all cells a2 thru j20

paste direct into the formula bar for A2,

then fill across to J2, then down to J100

.... also i get a invalid error for the k1 formula

You need to restore the couple of inevitable line wraps/breaks
which'll occur when copying and pasting long formulas from posts

Try copy > paste direct into the formula bar for K1,
then edit/restore the quite obvious line wraps/breaks
using the delete/backspace key


I could send you a sample book via private email
with the set-up implemented if you want.

Just drop me a line at either:

demechanik <at>yahoo<dot>com
xdemechanik <at>yahoo<dot>com

(both valid)

... is it possible for a formula to give me
... the number combinations to look up also?

Do you mean provide a random non-repeating pick of
a number of numbers from within a fixed range, say 1 - 80 ?

E.g.:
8, 10
25,47
13,16,19

Are you trying to generate bingo cards ?

If so, think Debra Dalgleish has a sample book at her:
http://contextures.com/excelfiles.html

(Look for Bingo Cards: BingoCard.xls
Scroll down - it's somewhere near the bottom of the page)