I have a bunch of data as seen below, the data continues on with A4, A5.....N10 etc. The equations Ive made use the cells 72D, 73D, 74D... 72H...74L. I dont want to have to move the equation cells each time when I get to A2, A3....N10 etc. I was thinking of using the Data in A1, delete it, then move everything up so that data in A2 takes its place and the equations work. I also need the worksheet to be named according to the data used, so one worksheet for A2, A3....N10 etc. Is there a quick way to do this?
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
If you move data into the row 72 to 74 region it will mess up the formulas using that region.
Better to use that region to extract the data you want, and copy all your data down 10 rows, so A1 starts in row row 83. You can then enter a region number say in cell C70 and generate row numbers in cells C72 to C74 and retrieve your data with something like =INDEX(D$83:D$xxxxx, $C72) and copy that down and across as required.
To save the file you can generate a file name say in C71 with: ="filename" & C70 & ".xlsb" and use some simple VBA to save the file with that name.
You might also want to review whether generating a huge number of separate files each with a copy of all the data is really the best way to do things. It would for instance be quite easy to copy the results of the formulas as values to somewhere in the one sheet, or just save the data you need from each region in the results files.
Thanks for the reply. How can I copy all data down 10 rows? The picture above is just an example, in reality I have a little bit more than 2000 rows, and there is no space inbetween all the data A1, A2...N10 etc.
Is this how you meant for it to be? Not sure what is meant to be in the "xxxx" part here: =INDEX(D$83:D$xxxxx, $C72)
I get the following error:
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
In other words, you don't move your DATA but you change your data POINTER.
And I'd assume a fixed number of rows between groups of data, like 10 rows, but, alas, the small snapshot of data shows 11 rows from 70 to 81 and the 10 rows from 81 to 91. So THAT won't work.
Suppose you tell us how we can determine how to predict the next group of data?
Skip, Just traded in my OLD subtlety...
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
One simple approach is to have one tab for each block of data and then set up your equations with all the tabs selected. This drills through the entire deck.
This is how my data looks like, three sets of data (Data A, Data B, Data C). This data goes from row 4 to row 2000 approximately.
In the same worksheet there is a function that takes the data and creates a text file which it saves locally on my computer, what I get out looks something like this, everything blacked out are standard numbers/text that wont change. The numbers are from the data seen in the picture above.
I need a text file in this format for all the 23-42, 23-43....and so on. The function that generates the text file uses the cells C6-C8, F6-F8 and J6-J8. How can I generate a text file for all data when I have a lot of it , for example C15-C17, F15-F17, J15-J17 and much more further down.
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
If you won't take the time and effort to display actual data rather than black lines , then don't expect other members to waste their time trying to figure out what you want or how to get there.
Quote:
How to use the date I have, delete it, and replace it with next set of data? [Excel]
How about SHOWING the data YOU have?
How about actually providing data that can actually be COPY n PASTED into an Excel workbook so someone can use it to provide YOU a solution?
Skip, Just traded in my OLD subtlety...
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
Look, I responded to your most recent post within 6 minutes with questions and waited over 2 hours, made some assumptions and posted a possible solution.
Are we getting closer? Hot? Warm? Cold?
Skip, Just traded in my OLD subtlety...
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
Thanks for taking the time Skip, I think its hard to explain without having a meeting, I was hoping to automate a task I believe I now should just do manually. There is no point in spending more time at this.
Red Flag Submitted
Thank you for helping keep Eng-Tips Forums free from inappropriate posts. The Eng-Tips staff will check this out and take appropriate action.
Reply To This Thread
Posting in the Eng-Tips forums is a member-only feature.
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Metal 3D printing has rapidly emerged as a key technology in modern design and manufacturing, so it’s critical educational institutions include it in their curricula to avoid leaving students at a disadvantage as they enter the workforce. Download Now
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
Better to use that region to extract the data you want, and copy all your data down 10 rows, so A1 starts in row row 83. You can then enter a region number say in cell C70 and generate row numbers in cells C72 to C74 and retrieve your data with something like =INDEX(D$83:D$xxxxx, $C72) and copy that down and across as required.
To save the file you can generate a file name say in C71 with: ="filename" & C70 & ".xlsb" and use some simple VBA to save the file with that name.
You might also want to review whether generating a huge number of separate files each with a copy of all the data is really the best way to do things. It would for instance be quite easy to copy the results of the formulas as values to somewhere in the one sheet, or just save the data you need from each region in the results files.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
Is this how you meant for it to be? Not sure what is meant to be in the "xxxx" part here: =INDEX(D$83:D$xxxxx, $C72)
I get the following error:
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
And I'd assume a fixed number of rows between groups of data, like 10 rows, but, alas, the small snapshot of data shows 11 rows from 70 to 81 and the 10 rows from 81 to 91. So THAT won't work.
Suppose you tell us how we can determine how to predict the next group of data?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
Another approach is to use indirect addressing.
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
- Select the entire data range.
- Press Ctrl-C
- Move down 10 rows
- Press Ctrl-V
As noted by skip, you will then need to move any data blocks that are not at 10 row spacing.
xxxxx just indicates any number greater than the last row number containing data.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
I COPIED the formula in D1 to D1:D4.
Then COPIED D1:D4 to H1 and L1
Now I can display ANY block by changing the value in C1:C4.
In other words, you don't move your DATA but you change your data POINTER.
Skip,
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
The big problem for me is to try to figure out what you really
wantneed.You tried to tell us the METHOD you thought would solve your problem, without telling us exactly what problem you were trying to solve.
I STILL DON'T KNOW WHAT YOUR PROBLEM IS.
Above is the solution to my GUESS of what you need.
I'll upload my sample workbook when I know that it meets your need/requirement.
Skip,
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
In the same worksheet there is a function that takes the data and creates a text file which it saves locally on my computer, what I get out looks something like this, everything blacked out are standard numbers/text that wont change. The numbers are from the data seen in the picture above.
I need a text file in this format for all the 23-42, 23-43....and so on. The function that generates the text file uses the cells C6-C8, F6-F8 and J6-J8. How can I generate a text file for all data when I have a lot of it , for example C15-C17, F15-F17, J15-J17 and much more further down.
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
How about SHOWING the data YOU have?
How about actually providing data that can actually be COPY n PASTED into an Excel workbook so someone can use it to provide YOU a solution?
Skip,
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
23-42 23-42 23-42
Red Red Red
9,7 0 9,3
11,1 6,4 7,9
89 47,7 64,7
0,2 0,2 0,2
9,3 0 9,3
-7,2 -5,8 -4,4
-45,5 -41,5 -25,8
23-43 23-43 23-43
Red Red Red
9,7 0 9,3
11,1 6,4 7,9
89 47,7 64,7
0,2 0,2 0,2
9,3 0 9,3
-7,2 -5,8 -4,4
-45,5 -41,5 -25,8
23-44 23-44 23-44
Red Red Red
9,7 0 9,3
11,1 6,4 7,9
89 47,7 64,7
0,2 0,2 0,2
9,3 0 9,3
-7,2 -5,8 -4,4
-45,5 -41,5 -25,8
Whats behind the black lines are just names of the author, description, date. Theyre not important in this case, can be whatever.
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
Furthermore, I'm further confused with how the data structure of your original post, figures in with the data structure of your latest post.
Skip,
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
My results using your data...
The formula...
G2:=INDEX(A$3:A$29,($F2-1)*9+1,1)
Just my guess.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]
Are we getting closer? Hot? Warm? Cold?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: How to use the date I have, delete it, and replace it with next set of data? [Excel]