CODEWITHSUNDEEP
excelauto-increment
user1551697
user1551697

Reputation: 21

Auto increment in excel for alternative cell?

Please check the eg.

row - value

1 - test1

2 - test1

3 - test2

4 - test2

5 - test3

6 - test3

Like this automatically have to increment in MS-Excel without using any scripts

Please give me a suggestion

Thanks in advance

Upvotes: 1

Views: 4039

Answers (2)

JimmyPena
JimmyPena

Reputation: 8772

This isn't exactly what you were looking for, and it requires a bit of a workaround, but it worked for me on a known quantity of rows.

It relies on Excel's built-in ability to figure out a fill series.

First, you set up your data like this instead. We skipped a row for each incrementing data point.

data setup

Then you highlight the data and include the row below it, like this:

data highlight

Now, since you are using two rows for each data point, drag the fill handle down to an even-numbered row:

data drag down

Now we use the well-known technique for filling in blanks:

  • Go to Special, Blanks:

go to special, blanks

  • Type =, ↑, Ctrl+Enter (equals sign, up arrow, Ctrl+Enter)

data formula

  • Highlight the values, copy (Ctrl+C) and Paste Values.

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32760

You can use a combination of the row number (ROW()) and rounding to get your desired result:

="test" & ROUND(ROW() / 2, 0)

Upvotes: 10

Related Questions