move & fill 1

[poli60]

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

 

[MortenA]

Have you tried "recording" a macro of these steps (with differential referemces turned on) and the look at the resulting code and see if you can use this?
Best regards, Morten

 

[SkipVought]

When you are using a REPORT to do analysis, you WILL encounter all sorts of obstacles and problems that you must rectify in order to use Excel's built in features.

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

 

[poli60]

Hi Morten.
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

 

[poli60]

Hi Skip.
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

 

[SkipVought]

As a matter of practice, reference tables are far better than nested IFs.

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