Solved

A very long series

Posted on 2012-04-07
5
347 Views
Last Modified: 2012-04-12
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
Comment
Question by:Humb13St3ps
  • 2
  • 2
5 Comments
 

Author Comment

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

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

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

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now