Macro or Formula needed to search data in cells

[hjopert]

Hi

I am looking for a way to create a formula/macro to do the following:

My worksheet setup:

A1:A30000 C1:C5000
01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27
02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32
03 04 05 06 07 08 09 10 11 12
04 05 06 07 08 09 10 11 12 13
etc

Each cell contains a 10 number sequence. The range A1:A30000 is my
randomly generated sequence. And C1:5000 is my database of archived
sequences.

If I wish to check if the combination in cell A1 is anywhere in the
range C1:5000. I use the formula.
=IF(COUNTIF(C1:C5000,A1),"bad sequence","pass")

What I would like to do is compare the combination in cell A1 to C1,
and if A1 contains 7 or more of the numbers in C1, the formula returns
the value "bad sequence". If it doesn't, continue to test the same
condition for C2, C3, C4 etc. If all cells in the range C1:C5000 pass
the test, then the formula returns the value "pass". Is there a macro
or super formula that could achieve this?


I don't know if this will help, but to partially solve my problem
above, I use text to columns. The generated sequence range becomes
A1:J30000, and the archive sequence range becomes L1:U5000. Then I use
the formula

=IF(OR((SUM(COUNTIF(L1:U1,A1:J1))>=7)),"bad sequence","pass")

Could I adapt this formula so that after it's tested L1:U1, it loops
through the other cells automatically until it reaches L5000:U5000?

Any help to find a solution will be most appreciated.

Regards

James

 

[hjopert]

Harlan Grove wrote:

multiple
instances of the same number?

Each cell contains a 10 number sequence, which does not contain
duplicates.

James.