Excel Spreadsheet

[dciscokid23]

Help please, I did not see an answer to my question anywhere (here or google), please forgive me if I missed it...

My problem is I have an excel document with rows number sequentially BUT some of those number are "missing" and I would like to do 2 things:
1) insert a row and 2) enter the next sequential number when it is not there. For example my excel worksheet has:

22143
22145
22151
22152
22155

And I'd like it to insert a row with the next number where missing like below:

22143
22144
22145
22146
22147
22148
22149
22150
22151
22152
22153
22154
22155

I actually have multiple worksheets with the same issue and thousands of numbers. (I am in telecom and this is for an extension database which could be from 10000 to 999999)

Thanks in advance,

 

[SkipVought]

HI,

Do you only have one column of data, presumably column A?

How many sheets?

Is this a one time exersize or will this be recurring?

Please answer each of these questions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

In my opinion you'll need some simple VBA code to do it.
If you want to go with the VBA route, ask your question in forum707

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dciscokid23]

Thanks for the replies:

Skip:

I will only need to do this once, to fix what we currently have. I also has 3 columns of data, extension, name and type.

Andy:

Thank you, I will post my question in the forum707: VBA Visual Basic for Applications (Microsoft).

Thanks again

 

[SkipVought]

If this is a one time deal, I wouldn't mess with VBA.

Each sheet has a MAX() & MIN() value. So on a separate sheet you 1) enter the min value at the top, 2) enter the next value in the next row, 3) select these two values and drag down until the maximum value is reached. Then in the adjacent column enter this formula and copy/paste down, assuming that row 1 contains a heading and column A contains the numbers and the source is in Sheet1...
[tt]
=INDEX(Sheet1!A,MATCH($A2,Sheet1!A:A,0),COLUMN())
[/tt]
Use this formula on each new sheet. Just change the sheet name accordingly.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

So you don't have just one column with data, you have 4 of them.
Something like;

[pre]
No Ext Name Type
22143 A Bob B
22144
22145 X Sue S
22146
22147
22148
22149
22150
22151 Z Zak Z
22152 X Joe J
22153
22154
22155 G XYZ X
[/pre]
I would use Skip's suggestion.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.