Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How to do a fill series in Excel

Posted on 2013-11-20
Medium Priority
309 Views
Hi experts,

Would you assist in providing a method to accomplish the following series in Excel?

1_1
1_2
1_3
2_1
2_2
2_3
...
150_1
150_2
150_3

Thanks
0
Question by:SASnewbie

LVL 35

Assisted Solution

mvidas earned 1600 total points
ID: 39662871
Hi SASN,

Enter the following formula in row 1 somewhere, then fill down through row 450:
``````=INT((ROW()+2)/3)&"_"&SUBSTITUTE(MOD(ROW(),3),"0","3")
``````
Matt
0

LVL 13

Expert Comment

ID: 39662876
If A1 has "1_1", enter following in A2 and drag down

``````=IF(--RIGHT(A1,1)=3,--LEFT(A1,1)+1,--LEFT(A1,1))&"_"&IF(--RIGHT(A1,1)<3,--RIGHT(A1,1)+1,1)
``````
0

LVL 35

Assisted Solution

mvidas earned 1600 total points
ID: 39662891
Shanan,

That will work as long as the first digit is only one character long. Change the LEFT(A1,1) part to include a FIND function to find the placement of the _
``````=IF(--RIGHT(A1,1)=3,--LEFT(A1,FIND("_",A1)-1)+1,--LEFT(A1,FIND("_",A1)-1))&"_"&IF(--RIGHT(A1,1)<3,--RIGHT(A1,1)+1,1)
``````
0

LVL 23

Accepted Solution

NBVC earned 400 total points
ID: 39662897
Try:

=MOD(INT((ROW()-ROW(\$A\$1))/3),150)+1&"_"&MOD(ROW()-ROW(\$A\$1),3)+1

copied down

you can change the 150 if you are going higher than that
0

Author Comment

ID: 39663113
Thank you all for your very fast responses!!!
0

LVL 43

Expert Comment

ID: 39663283
Yes I know the question is closed  but just to shorten Matt's formula:
=INT((ROW()+2)/3)&"_"&MOD(ROW()-1,3)+1
0

Author Comment

ID: 39663324
Thanks ssaqibh!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month10 days, left to enroll