Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel
|
|||
|
|||
Macro or Formula needed to search data in cells
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Macro or Formula needed to search data in cells
" wrote...
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? |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel
|
|||
|
|||
Macro or Formula needed to search data in cells
Harlan Grove wrote:
<sniped Could any of these cells contain duplicate numbers, that is, multiple instances of the same number? Each cell contains a 10 number sequence, which does not contain duplicates. James. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Macro or Formula needed to search data in cells
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and coloring cells, formula or macro help needed | Excel Discussion (Misc queries) | |||
Search/Sort Formula Help Needed | Excel Discussion (Misc queries) | |||
Search/Sort Formula Help Needed | Excel Discussion (Misc queries) | |||
Macro Search and Copy between Cells with unique data | Excel Discussion (Misc queries) | |||
Formula or macro needed for sorting complex data issue. | Excel Discussion (Misc queries) |