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

x
Solved

# A very long series

Posted on 2012-04-07
Medium Priority
356 Views
I have sets of very long lists of numbers ranging from 9,000 to 13,000 numbers long. I need to create a list where the first number in the list is placed in A1 for example, and then there are 9 blank cells, then A11 would contain the second number, 9 blanks, A21 would contain the 3rd number, etc. This is the first problem.

My second problem is this. There will be a repeating series of 9 numbers, in cells A2-A10 for example that will need to be inserted into the 9 blank cells in the series above.

If I cannot get this to work, I figure my best option is to copy and paste the repeating series of 9 numbers with a blank every 10th cell (which I can do easily), and then manually type in every 10th number. Since the series containing every 10th number is at least 9000 numbers long, this will take a considerable amount of time to finish each table. Any help would be greatly appreciated.
0
Question by:Humb13St3ps
• 2
• 2

Author Comment

ID: 37820639
Also, the series is listed in one spreadsheet, and the table being created is in another spreadsheet. The table cannot rely on the series to populate it's information. If one needs to do this at first to just creat the list and then copy and past just the text from the list created this would be fine. The spreadsheet containing the table just cannot permanently need the spreadsheet containing the series to pull information.
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 37820667
Without using VBA

Insert a blank column at A
- Number your data with an increment of 9 (one less than the number of lines desired per record) using fill series and starting from 0.5
0.5, 9.5, 18.5, 27.5...
- In the same column below your data fill another series starting from 0 and ending at the lat number of your previous series with an increment of 1.
- Now select All your data including column A to the bottom of column A values and sort it on column A. You will get the blank rows inserted.
0

Author Closing Comment

ID: 37820752
Ssaqibh,

This perfectly worked for creating the first series as described for my first problem. Your directions were very clear. I was able to complete the task by creating a series in Column B with the first cell blank, and the next 9 cells below containing the repeating series of 9 numbers. Using the fill series, I was able to extend this pattern to the sufficient length. I then used Column C, where the first cell was equaled to the same row in Column A, and the next 9 cells were equaled to their respective cells on the same row in Column B. Using fill series once again, I was able to complete this very long series in matter of seconds, and then copy and paste the text into the spreadsheet table I was creating. Thanks for the initial knowledge to see this through to completion!
0

LVL 43

Expert Comment

ID: 37821142
Hello, if everything went perfectly well then why a B grade?
0

## Featured Post

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month12 days, 3 hours left to enroll