Expand list by a certain number

[Ben]

Hi all,

I', regular reviewer of the Excel discussion groups and found it invaluable.
I can usually find the answers i need but not to this example(so thanks in
advance). I'm working with Excel 2003, and have a list that i want to expand
by a certain number. For example I want the set number in column A to
increase in groups of 20 assets:
A B
Set Asset
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12
1 13
1 14
1 15
1 16
1 17
1 18
1 19
1 20
2 21

So asset 21-40 will be set 2, asset 41-60 will be set 3 etc. I have 3000
assets(so far) and don't want to go through manually. This should be easy to
solve but I can't figure it out.

 

[Joel]

=mod(B1-1,20)+1

or you can use the row number

=mod(row(B1)-1,20)+1

 

[JBeaucaire]

I think it's even easier. Put a 1 in the first cell, copy it down so the
first set is done.

Now in the 21st cell enter =A1+1 and copy that down as far as needed. every
20 cells it will go up 1.

Then highlight the whole column, CTRL-C, Edit > Paste Special > Values.

Done.

 

[Dana DeLouis]

So asset 21-40 will be set 2, asset 41-60 will be set 3 etc.

Hi. Another way...

=INT((B1-1)/20)+1

And copy down.

= = = =
HTH
Dana DeLouis