Requesting a simple counting function, pretty urgent (VBA)

Hi all,

I basically have a list of several thousand 1s and -1s to sift through (the values represent a direction, 1 being left and -1 being right). The values are periodic, i.e. they go "1,1,1,1,-1,-1,-1,1,1,1,1-1,-1", etc but the period is not regular - in fact, that's what I'm trying to work out.

So in effect, what I need is a loop that will start off reading the cell E4, see that it is "-1", then sum downwards through the E column until it hits a 1, in which case it will save the sum of all -1s to another cell. Then, keep counting the "1"s until it hits a -1, save the number of "1"s to another cell, and so on and so on until it hits a blank or 0 value.

In the end, what I basically need is an ordered list of values, with the positive values in one column, and the negatives in another i.e. A1 = 34, A2=44, B1=-30, B2=-55.

Much gratitude if you can help. My VBA skills are very rusty and I have never worked with it in Excel (only MS Access).

Originally Posted by ssdt

Hi all,

I basically have a list of several thousand 1s and -1s to sift through (the values represent a direction, 1 being left and -1 being right). The values are periodic, i.e. they go "1,1,1,1,-1,-1,-1,1,1,1,1-1,-1", etc but the period is not regular - in fact, that's what I'm trying to work out.

So in effect, what I need is a loop that will start off reading the cell E4, see that it is "-1", then sum downwards through the E column until it hits a 1, in which case it will save the sum of all -1s to another cell. Then, keep counting the "1"s until it hits a -1, save the number of "1"s to another cell, and so on and so on until it hits a blank or 0 value.

In the end, what I basically need is an ordered list of values, with the positive values in one column, and the negatives in another i.e. A1 = 34, A2=44, B1=-30, B2=-55.

Much gratitude if you can help. My VBA skills are very rusty and I have never worked with it in Excel (only MS Access).

You don't necessarily need any VBA to do this. Let's assume this column of 1s and -1s is column E starting in E2. Enter the following in A2 and copy down:

=IF(E2<>E3,ROW()-1-SUM(A1:A$2),"")

and similarly in a helper column say B enter the following in B2 and copy down:

=IF(A2="","",ROW())

Column A will give you the running sequences and column B is the row number. This is needed in the sort now to come. Copy columns A&B and use PasteSpecial Values to convert the formulas to values.

Now sort columns A&B using B as the Sort key Ascending. You'll now have a table of running sequences in column A, and you can delete the helper column B if necessary.

If you need to do this across several sheets and/or books you'd probably want to automate the population of the formulae and the sorting etc with a macro.
HTH

Thanks, you're a life-saver

I did as you said and it all worked nicely, with a bit of multiplying by -1 and SUMIF/COUNTIF and such I got it exactly as required.

Thanks again for the help. :D