Analysing Array Data

Is there several arrays to prepare, if yes how is the display ?

Is the Array in VBA or Excel?

It is simple to do what you want using string. The Array route is a lot more work.

Number of "11111" in string "0,1,1,1,0,0,1,1,0,0,0,0,1,1,1,0,0,1,1,1,0,0"

Is: (Len("0,1,1,1,0,0,1,1,0,0,0,0,1,1,1,0,0,1,1,1,0,0") - Len( Substitute("0,1,1,1,0,0,1,1,0,0,0,0,1,1,1,0,0,1,1,1,0,0", ",1,1,1,1,1","") ))/10

You can do something Similar in VBA

A = Join(ArrayA, "")

Qty = (Len(A) - Len( Substitute(A, ",1,1,1,1,1","") ))/10

What is the maximum number of continuous 1

Please Login or Register  to view this content.

For the data lay out, can I suggest:
Column A = 111100000001111000110001110000011100011110000111110000110000111111
Column B = Nb of 1
Column C = Nb of 11
Column D = Nb of 111
etc ...
Is it ok ??
BTW There no anymore comma (,) ???

If the layout suggestion is good for you see next code and file attached

Please Login or Register  to view this content.

The code from Mehmetcit earlier works perfectly (thank you for this), but struggling to populate A from a column which has the numbers. (Although still trying to understand the process)

Please Login or Register  to view this content.

As a bully deleted my working VBA procedure …

If at least you have attached a workbook with a good enough explanation in the initial post like any forum expect for
you already have a solution since post #2 as, after all, this is very not a mind reader forum.
So now you know …

Can I populate it like this?

Please Login or Register  to view this content.

Administrative Note:

We would love to help you with your query; however, you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Try this

https://www.excelforum.com/tips-and-...geravatar.html

Not sure I know of a limitation, but how long of a column are your taking about?

I just tried it with 6000 rows and it works fine. I did see a limitation with around 7000 something rows, but can't remember the number. Check this out.

Thanks. I have modified this to work in column A.

The first two lines put test data in column A. These can be deleted.

Please Login or Register  to view this content.

Mehmetcik,

Is it possible to just briefly explain the process of the code?

I can't work out the method you are using.

Thanks

Modified to put test array in I6 to I70.

Once Tested delete rows 1 and two and put your array in range I6 to I150000


This macro will create a randon array for you.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[/CODE]

@ mehmetcik

A = zero-based so you need this

Please Login or Register  to view this content.

to display full Array A.

Adapted Rick's code to work from I6 to undefined endrange.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

@ mehmet

In Post#26 OP talks about 55.000 rows.

Straight from Microsoft docs.

The result of the Rept function cannot be longer than 32,767 characters, or Rept returns the #VALUE! error value.

Thanks Bakerman

I have just found another limitation. The join function returns a string of max length 23923 characters.

So need to find another solution.

Please wait 10 mins.

I have modified my code (besides making it start at I6 which I missed from an earlier message) and it handled 65000 rows of data in about one second on my somewhat slow laptop. See if it works as well for you. Remember, though, that there is 65355 row limit on what this macro can handle.

Please Login or Register  to view this content.

If you are interested, the output from my test run of 65000 rows of data was this...

Seq of 1 8157
Seq of 2 4058
Seq of 3 2032
Seq of 4 1022
Seq of 5 520
Seq of 6 262
Seq of 7 124
Seq of 8 60
Seq of 9 25
Seq of 10 21
Seq of 11 8
Seq of 12 3
Seq of 13 0
Seq of 14 1
Seq of 15 1

Thanks Rick. I found the issue.

The Application Transpose that I use to simplify addressing seems to bin anything over 23000 entries.
So my original solution would work without the transpose.


Ok completely different solution.

Using a helper column and Excel formulas to avoid Array and Rept limitations.
This should be faster so I will stick with this.

Please Login or Register  to view this content.

Hi mehmetcik,

Hi, My data begins from cell I6 downwards, can I modify this

Please Login or Register  to view this content.

to check from I6 downwards?

Also the code above has a type mismatch error and I haven't modified it.

Thanks again for all your help.

Hi Rick,

Your code works really well, caters for all lengths of 1. Is there any chance you could put some comments so I can understand what is going on?

Much appreciated!

Hi Cerkit,

Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

Originally Posted by Cerkit

Thank you for the code, works perfectly. Could you please help me understand what the following line in your code does?

Please Login or Register  to view this content.

I replaced the line of code you asked about in my quote above with the complete loop as they are tied together. The code comes from way back (more than 16 years ago) in my volunteering days for the compiled version of Visual Basic (the version prior to what Microsoft now calls Visual Basic, originally known as Visual Basic DotNet). What those numbers do is guarantee to reduce multiple contiguous characters (usually, spaces) within a text string down to single characters. For spaces, it performs the same function as Excel's TRIM worksheet function (except for removing any resulting leading or trailing spaces... it does not do that), although it can be used on any character delimiter. Given that VBA gives you access to Excel's TRIM function via the WorksheetFunction object, you may wonder why I did not simply use that. While the Evaluate function along with WorksheetFunction.Transpose, WorksheetFunction.Index, etc. can handle 65,535 rows of data, WorksheetFunction.Trim appears to be limited to being able to handle a text string of 32767 total characters or less. If I remember right, you said you had 50,000 or so rows of data, so when I combined them into a single text string and replaced the 0's with spaces, it was too long for the Excel's TRIM function to handle. The loop I ended up using can successfully collapse multiple spaces to single spaces in a text string of up to 64,570,080 total original characters in length. Now, where did those numbers come from you might ask. I cannot take credit for developing them although I tracked the original newsgroup (precessessor to Forums) thread in real time back then. Here is a direct link to the message where those numbers were proposed...

https://groups.google.com/d/msg/micr...U/eMg5CWUBge0J

If you scroll up to the top, you can see the question that started this thread and then read each message under it in order to see how the final numbers grew to be developed.