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

 

[Harlan Grove]

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.

....

Could any of these cells contain duplicate numbers, that is, multiple
instances of the same number?

 

[hjopert]

Harlan Grove wrote:

multiple
instances of the same number?

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

James.

 

[hjopertham]

Hello

No responses so far, so I will simplify my requirements.

The range A1:J30000 is my randomly generated number sequence. And
L1:U5000 is my database of archived sequences.

My worksheet setup:

A1:J30000 L1:U5000
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

If I wish to check if A1:J­1 contains 7 or more of the numbers in
L1:U1. I use the formula:

=IF(OR((SUM(COUNTIF(L1:U1,A1:J­1))>=7)),"bad sequence","useful
sequence")

Ideally if the formula fails the 'logical test', I would like it to
continue testing the next row in the range ie L2:U2, L3:U3. If any row
causes A1:J1 to pass the 'logical test' the formula stops and returns
the result "bad sequence". If all rows in the range L1:U5000 are tested
and A1:J1 fails the test, then the formula returns the result "useful
sequence".

Is it possible that someone could provide a macro or UDF solution to
achieve the above so that after it's tested L1:U1 it automatically
loops through the other rows until it reaches L5000:U5000?

Please note I need to be able to adapt the range and 'logical test'.

Regards

James.