Reformat contents of template

Posted on 2011-10-10
Last Modified: 2012-06-27
I have a template which I have created in Excell which has now been populated with information.
The template is a list of individuals with corresponding descriptions, comments, and budget figures.
There are 6 rows for each individual.
My goal is to remformat the content into one logical row of data for each individual( ID# - September Budget %) that I can then import into an access database.

For the columns "Projects" (K) and "Comments" (L) that may have 6 individual rows of data, I would like to string the comments together and Ideally have them combined into one cell.

For columns S- AE (The Forcast section of the template) I would like it to be formated in the one row as "Opex Budget %" followed the 12 figures for each month (S:6 AE:6) , then "Capex Budget %" followed by the 12 figures for each month (S:8 AE:8)

I have attached a mock version of the template with filler data for the first few resources.

Is it possible to accomplish this through using some VBA code to reformat the template?

If the hidden columns M-Q create any difficulty they can be deleted.

Thank you.

Question by:jtr209
    LVL 3

    Expert Comment

    This can be accomplished without VBA.

    Join all cells into one cell with the & sign:
    example, type this into cell AF5     =K5&" / "&K6&" / "&K7&" / "&K8&" / "&K9&" / "&K10
    I have added a space + slash + space for ease of reading in the combined cell.
    It is not fully clear to me what cells you want to combine in what way, but anyway, you sure get the hang of this and can adjust the formula to your likes.
    Complete all desired combined formulas on row 5.

    Then apply a filter from column C to the last column containing your selfmade formula.
    Filter on column C - only the non blanks.
    Then copy your selfmade formulas all the way to the bottom of your list.

    When your template has been filled with data, you can use the filter once again, and copy the results as values to another sheet. From that sheet you can easily import to an access database.

    However, if you're a bit familiar with access you should be gathering this data in access straight away. That's why access is there for ;-)


    Author Comment

    Thanks for the response.

    The join cells with the & sign works for me for the two columns "K" and "L" perfectly.

    However the second portion of the template the "FY 12 Forcast" section I dont want to combine the cells, rather I want to have the "Opex Budget" row  S:6 -  AE:6 move up to row 5
    and also the "Capex Budget" row S:8 - AE:8 also move up to row 5 following the "Opex Budget" row
    The goal being to have everything in one row but in individual cells.

    Really appreciate the help.

    LVL 3

    Accepted Solution

    Why don't you use linked cells?
    cell AH5: =T6
    cell AI5:  =U6

    See attach for example.
    But again, in my opinion you should use an Access database for these type of excercises.
    Excel is a calculator and should be used as such. Access is a data administrator.


    Author Comment

    Ok thanks, that is kind of what I was just figuring out.

    The template is already completely filled out - I just sent a mock copy.
    It was a coloaborative effort and using access for the data entry was not really an option.

    Thanks also for he detailed example.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now