×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

move & fill

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 smile
poli

RE: move & fill

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

RE: move & fill

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: move & fill

(OP)
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 sad
Thank you!
poli

RE: move & fill

(OP)
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

RE: move & fill

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

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.

Click Here to join Eng-Tips and talk with other members!


Resources