Hi All,
I'm a bit of a newbie at this, so pardon this possibly basic question. I have a 37,000 or so line spreadsheet, containing one column of only 1's and 0's. I need to find all of the instances in which I have 50 (or more) repeating 1's in a row. I know this should be able to be done with a Do until loop, but I'm having difficulty. Any help?
This assumes your list of 1s and 0s is in column A from row 1 to row 37,000, and the results will be put in column B. Note that looping through 37,000 rows will take some time.
Please Login or Register to view this content.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Seems like this looks good, and makes sense, though I'm getting nothing when I run this...nothing at all, actually.
If you'd care to post a sample workbook, I'd be willing to take a look.
I would love to do that, but the data is clinical trial data and I cannot legally share it. All I have is 351,508 (I mistyped before) rows of 1's and 0's in a column ("I"), where I need to look for these >=50 long repeats. There are probably only 1-2 in the entire data set .... tedious!
Hi mthpsu, try running the following codePlease Login or Register to view this content.
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
Some how I am not sure if this right!
I tried running it, but it put my excel into a not responding mode! Yikes!!
Oops, sorry about that! Maybe I need to define the range. Give a 2 secs and a shake!
Try this, hopefully it won't crash your excelPlease Login or Register to view this content.
mthpsu,
Throwing in my 2 cents. This macro uses the autofilter to find all groups of 1s in column I. It then highlights all instances of 50+ consecutive 1s and displays a message showing how many it found
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Last edited by tigeravatar; 06-23-2011 at 06:10 PM.
This seemed to work on a small subset of my data, but when I ran it on the whole thing, I got an error (too complex). I removed all the filters and still had the same problem. Thanks for all your help - this is just tougher than I thought it would be!
I also tweaked it a little, and it seems to be working (no instances of 50+ - ack). Though this line "Set rngI = rngI.SpecialCells(xlCellTypeVisible)" is giving me trouble from time to time.
You could use this UDF.
The formula =ConsecutiveColumnEntryCount(1, I:I, 50) will count the number of 50 consecutive 1's in column I. (note 51 consecutive 1's counts as 2 runs, I1:I50 and I2:I51)
Please Login or Register to view this content.
Last edited by mikerickson; 06-23-2011 at 09:10 PM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
I ran tiger's code on 900000+ rows, with 7500 + instances of 50 or more with no errors. Nice.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks