jtr209
asked on
Reformat contents of template
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.
Budget-Template-With-Input-2.xls
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.
Budget-Template-With-Input-2.xls
Last Comment
ASKER
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.
Thanks
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.
Thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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 ;-)
Davy