Special sequence

[shimaa01234]

I want to sequence such as this example
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl24, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"]2[/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"]3[/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"]4[/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

There's probably a shorter way but try this

=IF(MOD(ROW(),4)=1,INT((ROW()-1)/4)+1,"")

 

[XOR LX]

There's probably a shorter way but try this

=IF(MOD(ROW(),4)=1,INT((ROW()-1)/4)+1,"")

What if the OP doesn't want to begin the sequence in row 1?

ROW vs ROWS for consecutive integer generation | EXCELXOR

Regards

 

[shimaa01234]

Thank you very much Mr. "Special-K99"
Very brilliant formula
And works like a charm.

 

[Special-K99]

What if the OP doesn't want to begin the sequence in row 1?

ROW vs ROWS for consecutive integer generation | EXCELXOR

Regards

Then he should specify that in the description, otherwise the assumption is row 1, though I do prefer your solution and have started to use that in solutions.

 

[RoryA]

You could also use autofill of course.

 

[XOR LX]

Then he should specify that in the description, otherwise the assumption is row 1, though I do prefer your solution.

Perhaps, but the whole point is that the OP never need mention a word about where they want to start the sequence if we use ROWS, not ROW in our solution!

The main reason for writing that article is that I became so surprised to find that the vast majority of people still use a construction involving ROW in these situations, despite its obvious drawbacks and the fact that there is a much more rigorous alternative available.

And the number of times I've seen OPs come back saying something along the lines of "I put the formula in A6 and the results aren't what I was expecting", only to be met with "Oh, well if you don't want to begin the formula in row such-and-such then you need to add/subtract such-and-such from the ROW part..." is not inconsiderable.

But of course all this extra conversation/confusion about adding/subtracting (not to mention the other drawback of being susceptible to row insertions) can be prevented if people would simply stop using ROW in these situations! It really makes no logical sense!

Regards

 

[shimaa01234]

wonderful link , from wonderful person
Mister "XOR LX"
I love all your business

 

[XOR LX]

wonderful link , from wonderful person
Mister "XOR LX"
I love all your business

That's very kind of you!

Cheers!