How to do a fill series in Excel

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
SASnewbieAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
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
 
mvidasConnect With a Mentor Commented:
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")

Open in new window

Matt
0
 
Shanan212Commented:
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)

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mvidasConnect With a Mentor Commented:
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)

Open in new window

0
 
SASnewbieAuthor Commented:
Thank you all for your very fast responses!!!
0
 
Saqib Husain, SyedEngineerCommented:
Yes I know the question is closed  but just to shorten Matt's formula:
=INT((ROW()+2)/3)&"_"&MOD(ROW()-1,3)+1
0
 
SASnewbieAuthor Commented:
Thanks ssaqibh!
0
All Courses

From novice to tech pro — start learning today.