Incrementing each row in excel that contains a number

Question

I was sent an excel document in the following format:

A |  B  |
1 | abc |
  | def |
2 | abc |
  | def |
  | ghi |
3 | abc |
  | def |

So basically I have the first column which contains ordered numbers every couple of rows. I want to insert a row e.g. between 2 and 3, so that the new row will be numbered 3, and the rows below it are updated accordingly i.e. 3 becomes 4, 4 becomes 5 and so on.

I don't really use excel, but I am curious if there is there an easy way of doing this?

in which language you want to do.

Romil Kumar Jain
– Romil Kumar Jain

2012-05-31 10:49:35 +00:00
Commented May 31, 2012 at 10:49 — Romil Kumar Jain
– Romil Kumar Jain, Commented May 31, 2012 at 10:49

assylias · Accepted Answer · 2012-05-31 10:49:12Z

1

In A1, enter the following formula:

=IF(MOD(ROW(A1),2)=1,(ROW(A1)+1)/2,"")

And copy that formula down to the bottom of the range. Each time you insert a new row, you will need to manually copy the formula to the inserted row's first cell.

answered May 31, 2012 at 10:49

assylias

330k84 gold badges680 silver badges806 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arnoud Kooi · Accepted Answer · 2012-05-31 10:51:26Z

1

Is there a pattern in the b column, to distinguish if you need to jump to the next number? if so use: in cel A2 and further:

=IF(B2 = "abc",A1+1,A1)

With conditional formating, hide the repeating numbers: =A2=A1

answered May 31, 2012 at 10:51

Arnoud Kooi

1,7675 gold badges18 silver badges29 bronze badges

1 Comment

Matt Over a year ago

The B column contains descriptions, so each row will be different. It was solved above, but thanks for the help! :)

Collectives™ on Stack Overflow

Incrementing each row in excel that contains a number

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related