move & fill
move & fill
(OP)
Hi Experts.
In a previous thread (http://www.eng-tips.com/viewthread.cfm?qid=392521), Doug and Skip kindly helped me on a "copy & fill" problem.
Now I have a similar question relevant to the attached file...
Orange shaded cells in col A of the tab "ORI" contains data that I'd like to
1) move to a new adjacent column on the left
2) move down one position
3) delete the created empty row
4) copy down in the empty cells (filling the space between two orange cells)
... so that the modified table will appear as in tab AUTO.
Step 3 and 4 are solved but I've no idea about automation of steps 1 and 2.
Grateful for any other help you can provide
poli
In a previous thread (http://www.eng-tips.com/viewthread.cfm?qid=392521), Doug and Skip kindly helped me on a "copy & fill" problem.
Now I have a similar question relevant to the attached file...
Orange shaded cells in col A of the tab "ORI" contains data that I'd like to
1) move to a new adjacent column on the left
2) move down one position
3) delete the created empty row
4) copy down in the empty cells (filling the space between two orange cells)
... so that the modified table will appear as in tab AUTO.
Step 3 and 4 are solved but I've no idea about automation of steps 1 and 2.
Grateful for any other help you can provide
poli





RE: move & fill
Best regards, Morten
RE: move & fill
This is a classic example of a pivot report:
1) Only first instance of data groups are displayed
2) Multiple data fields are stacked in the same column
3) Data is used as column headings
4) Aggregations have destroyed source data granularity
When this occurs, you may even have objectives that will be impossible to achieve.
The ultimate solution is to find the source data that was used to produce the report. I have worked at three aerospace companies where I was able to solve issues like this be getting access to tables or flat files generated directly from tables. If you were able to do this, it would eliminate these issues that you are attempting to solve.
Short of that, I'm guessing that you're getting a file on a recurring basis, that you need to condition in order to become useful as an Excel file for analysis. I'd build a reference table of Countries. So fill in the empty cells, loop through the Auto Make field to find the countries and build your expanded table.
Then you're just beginning your sorrows. You have 15 columns where the heading is actually a data value (the YEAR) So if you ever wanted to get data involving multiple years, something a formula could do if your data were normalized (which it is not) it will be difficult. I use the Structured Tables feature and Named Ranges. So my formulas almost never have A1 or R1C1 referencing. You have a difficult row to hoe.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: move & fill
I'm used to read your interesting posts in petroleum / refining / chemical sections of eng-tips (and also on cheresources)....
I think that, in this case, the "recording" tecnique is not useful since the position of the orange cells is, let me say, random.
....unless it is possible to look for orange cells. For the time being I didn't succeed in this effort
Thank you!
poli
RE: move & fill
Your analysis is right: that's the kind of origin for my data... but I have no access to the original DB... originates in the 70s.
Now I'm going to consider your suggestions about the reference table sinceI've to look always for the same 31 countries / regions.
Thank you,
poli
RE: move & fill
Skip,
Just traded in my OLD subtlety...
for a NUance!