How to use the date I have, delete it, and replace it with next set of data? [Excel] 1

[bojoka4052]

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?

 

[IDS]

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$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/

 

[bojoka4052]

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$xxxxx, $C72)

I get the following error:

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[GregLocock]

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.

Another approach is to use indirect addressing.



Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[IDS]

To copy the data down 10 rows:
- 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/

 

[SkipVought]

Here's my take.

I COPIED the formula in D1 to D14.

Then COPIED D14 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

@bojoka4052,

The big problem for me is to try to figure out what you really want need.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bojoka4052]

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.

 

[SkipVought]

If you won't take the time and effort to display actual data rather than [highlight #000000] black lines [/highlight], then don't expect other members to waste their time trying to figure out what you want or how to get there.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bojoka4052]

Data A Data B Data C

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.

 

[SkipVought]

Then what logic are you using to get from Data A, Data B, Data C to the explicit result. Its just a "black box" UNKNOWN transformation.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Well I'm guessing again. I guess that will get me into trouble.

My results using your data...
[pre]
F G H I
Group Data A Data B Data C
1 23-42 23-42 23-42
2 23-43 23-43 23-43
3 23-44 23-44 23-44
[/pre]

The formula...
[tt]G2:=INDEX(A$3:A$29,($F2-1)*9+1,1)[/tt]

Just my guess.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bojoka4052]

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.