?
Solved

How to do a fill series in Excel

Posted on 2013-11-20
7
Medium Priority
?
312 Views
Last Modified: 2013-11-20
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
Comment
Question by:SASnewbie
7 Comments
 
LVL 35

Assisted Solution

by:mvidas
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")

Open in new window

Matt
0
 
LVL 13

Expert Comment

by:Shanan212
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)

Open in new window

0
 
LVL 35

Assisted Solution

by:mvidas
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)

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 23

Accepted Solution

by:
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

by:SASnewbie
ID: 39663113
Thank you all for your very fast responses!!!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

by:SASnewbie
ID: 39663324
Thanks ssaqibh!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question