A very long series

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.
Humb13St3psAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
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
 
Humb13St3psAuthor Commented:
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
 
Humb13St3psAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Hello, if everything went perfectly well then why a B grade?
0
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.

All Courses

From novice to tech pro — start learning today.