Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Will Not Automatically Expand | Excel Discussion (Misc queries) | |||
Excel (2003) list does not expand | Excel Discussion (Misc queries) | |||
Dynamic source list to auto expand | Excel Discussion (Misc queries) | |||
expand number of rows from limit of 65,536? | Excel Discussion (Misc queries) | |||
Can I expand a drop down list? | Excel Worksheet Functions |