PDA

View Full Version : Solved: Consecutive Numbers Define range



fredlo2010
07-05-2012, 03:05 PM
Hello guys,

I am trying to define a range based on a column that has consecutive numbers.
My Column "H" has this data:

1
2
3
1
2
3
4
5
1
2
1
2
3
4
5
6
7

I want to copy a formula on column "J" in an alternating way. For my first block of consecutive numbers I want to add to the row in column "J" (rows values have been omitted) G+H and for the next block then G+H/H so on alternating each block with this two formulas.

Any ideas?

I was thinking to check the current cell with the value of the one on top.

Here is the code I managed to put together. :(

Dim i As Long
Dim ii As Long ' this will be the count of consecutive values but I cannot find a way to define it

lastrow = Range("H" & Rows.Count).End(xlUp).Row

With Range("H2:H" & lastrow)

For i = 1 To lastrow

If .Cells(i, 8) = .Cells(i - 1, 8) Then

Cells(i, 10).Resize(i + ii, 10).FormulaR1C1 = "=RC[-3]+RC[-2]"

Else
.Cells(i, 10).Resize(i + ii, 10).FormulaR1C1 = "=RC[-3]+RC[-2]/RC[-2]"

Next i

fredlo2010
07-05-2012, 06:05 PM
my code is totally wrong

What I need is a fomula to count recurrences and them use MOD to get a 0 and 1 to separate my ranges.

This is the formula I managed to put together

=MOD(COUNTIF($H$2:H2,H2),2)

It works untill I have a new value in my data column. So if I have

1
2
3
1
2
3
4
It will work for all numbers excelpt for value 4 whcih has a count of one.

mikerickson
07-06-2012, 12:38 AM
How about
=MOD( COUNTIF($H$2:H2, MIN($H$2:H2)) ,2)

or
=MOD( COUNTIF($H$2:H2, $H$2) ,2)
or
=MOD( COUNTIF($H$2:H2, 1) ,2)

fredlo2010
07-06-2012, 08:15 AM
Yep those work perfectly.

Do you guys mind helping me through the thinking

mikerickson
07-06-2012, 10:33 AM
I'm not sure, but it looks like a new "group" starts every time that the value changes back to 1. Thus COUNTIF(b$2:b2, 1) will count the number of groups that have begun on the row with the formula or above.

It looks like, as time goes on (or rows go down), you are adding new members to the group. This formulation works for that, BUT, if you are also removing members as time goes on, if member "1" is removed, the COUNTIF formulation will fail.

In that case SUMPRODUCT(--($B$3:B3<$B2:B2)), would have to be used to count the number of groups above.

fredlo2010
07-06-2012, 10:54 AM
Thanks a lot mikerickson, crystal clear now. For some reason I was failing to see what MIN was doing. Its in fact referring to "1" which acts as the group delimiter.

Does the <quote>I'm not sure</quote> come in another flavor other than " educational mockery" ?:rotlaugh:

Thanks a lot for the help. Once again.

mikerickson
07-06-2012, 11:52 AM
"I'm not sure" means that I am not aware of where the numbers come from. and how the user will enter the data.

The question of whether the user might remove a number from the group is part of "I'm not sure".

fredlo2010
07-06-2012, 12:00 PM
lol Sorry I misunderstood you. In a funny way though :) and yes the numbers are part of a cycle that will always start with a "1"

Thanks a lot