Hello,

I have a spreadsheet with multiple tabs that lists (via Macro) all possible combinations for 15 numbers out of 25 numbers (3 million+ results - 230Mb xls), where each number is on cell.
Now I want to clean up this list removing all the combinations that have more than 6 numbers in sequence (actually replace the cells with blank).

For example:

1, 2, 3, 4, 5, 6, 7, 10, 14, 15, 17, 22, 23, 24, 25 - since we have 7 numbers in sequence (1 to 7), I would like to replace all 15 cells in this row with blanks.

Also, I would like to remove some patterns, for example:

1, 3, 5, 7, 9, 11, 13, 15, 17, 18, 19, 21, 23, 24, 25 - since we have 1 to 17 skipping one number (than I would select the patterns I don't want and replace all 15 cells with blanks too).

Any ideas??

Tks, Marcus

Could be done with a little legwork with equations. Code might be better. Question: are the numbers in a row always in sequential order? i.e.

1, 2, 3, 4, 5, ...
but not
1, 5, 3, 2, 4, ...

If so,

=if(or(and(a2=a1+1,a3=a2+1,a4=a3+1,a5=a4+1,a6=a5+1 ,a7=a6+1),(same thing comparing cells a2-a8),(so on and so forth),(same thing comparing a19-a25),"x","").

Then all you have to do is sort by those results and clear any rows that has an "x" for this result. I'm not sure if Excel limits on formula size will be a problem. I see a limit of 1024 characters per cell so if that applies to formulas, you should be ok. ***

Anyhoo, same sort of thing with your other rules. If the above equation works, you should be able to figure it out from here. If not, let me know.




*** I think some equations have some quirky limits to like a Sum equation can't have more than 30 commas). You should be ok. I actually just tested that and it seems like that is no longer an issue.

All the numbers are in ascending order. I'm guessing the best approach here will be through coding, because than I would only need to run the script to clean up the xls.

As I mentioned before, it's a 230Mb xls with 5 tabs and 3 million+ combinations

Yeah, but it's the same amount of work to write the VBA code to run through millions of rows as twelve. Just takes longer to run afterward. And there are things you can do to keep even that from getting too unwieldy—or at least to reduce the run time considerably, but 3 million rows may take a while no matter how cleverly you code.

So your data is in 3 million rows by 15 cells, right? Do you know how you want to proceed, then, or are you still looking for ideas?