Expand list by a certain number
 

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.

Expand list by a certain number
 

=mod(B1-1,20)+1

or you can use the row number

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


"Ben" wrote:

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.

Expand list by a certain number
 

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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Joel" wrote:

=mod(B1-1,20)+1

or you can use the row number

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


"Ben" wrote:

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.

Expand list by a certain number
 

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


Ben wrote:
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.