VBA code required to convert monthly Excel report into an Excel Table
£10-20 GBP
Completed
Posted over 6 years ago
£10-20 GBP
Paid on delivery
Each month I receive an Excel report. I want to combine all the monthly reports into one worksheet of one workbook.
The 1st step is to write VBA code to run in the personal macro workbook which will organise this report into an Excel table. The report is not organised into neat columns. It has been designed to be printed out, page by page and viewed as such.
The 2nd step is for the table to be attached to an existing (several previous months) table in another, target workbook. In this way an historical listing of all entries will be built up in the target workbook's worksheet. (In the target workbook, the table headers will have to be inserted one time only, manually before the intial entries are placed in the worksheet. Thereafter, the monthly data should simply be attached to the bottom of the previous month's data).
The key tasks at step 1 are to:
Move data for each entry which appears in the "wrong" column/field into a newly created one;
Eliminate report page headers and footers;
Concatenate items of data lying in different rows of the same column (e.g. lines of an address appearing in successive rows below each other, but all in the same column) and move them to a newly created column/field;
Allow for variation in the number of rows occupied by each entry (e.g., 2, 3 or 4 rows used by an address in one column);
Allow for blank cells in some entries.
The key task at step 2 is to move the data from the source workbook into the target workbook at the bottom of the historical rows of data.
The VBA code should be well written and annotated with comment lines describing clearly each step in the procedure.
Further possible work: I receive a number of other reports in the same manner and there will be a future need to carry out the same exercise for some of these.
I have taken some time to create a sample set of dummy data (names and addresses, etc.) by editing an existing report. I have uploaded the dummy workbook to this project website.
The reports come in the Excel 2 format, so any software policy restriction would have to be removed by yourself.
Note, unfortunately, the source workbook's worksheet name is not standardised in the same format each month. In practice, different people produce this workbook and, although generally, the worksheet name will be in the same format, e.g. "Report (07-10-2017-0344-07).XLS", that format can vary from month to month.
So far, the fields to be used as table column headers in the target workbook that I have identified in the Excel report are:-
Account Code
Active
Client name
Client description
Created
Managing partner
Client Type
Source of business
Client address
You may have a different view on the fields/columns that are required.
I look forward to your quotation.
Thanks
John
Hi John, from the description provided, i am able to script the macro accordingly to the step. However, may need further verification with more details. Thanks
Relevant Skills and Experience
visual basic application( Excel VBA ), visual basic script
Proposed Milestones
£18 GBP - Full completion
Additional Services Offered
£10 GBP - Further new enhancement or improvement after completion
I may need the sample on (monthly excel report, output )
£18 GBP in 3 days
4.3 (2 reviews)
1.9
1.9
11 freelancers are bidding on average £71 GBP for this job
I can do it for you quickly and exactly . Please contact me
Relevant Skills and Experience
I have finished great than 60 project in this site. Please see my portfolio for more detail
Proposed Milestones
£50 GBP - all
"I am an Excel Expert with Professional experience working with Excel both with Formulas and Macro- VBA. Working with complicated formula and Developing Macro programs are part and parcel of my daily work.
I am a Business (MBA) Graduate with major in Finance and MARKETING along with 5 years of experience in different analytical roles. Currently I am working for a Multinational Telecommunication Company as an B2B Analyst Manager.I believe I can do a fantastic work for you if you provide me the opportunity. "
I have 7 years of experience with 100% client satisfaction history. See my profile for client reviews about me. Yes my bid higher as this work require ample time and concentration.
Relevant Skills and Experience
Excel VBA
Proposed Milestones
£166 GBP - Complete Solution
Looking for your response
Hi
I am here to assist u inthis project
I am an ACCA member currently working in finance dept of Qatar Airways
Have great skill and experience in Ms excel can assist u with high regards
Plz share more info to proceed
Thanks in anticipation
Looking forward for this projext positively
U can also review my profile for my clients reviews of my excel skills
Dear client,
I have expertise in VBA. Can show you previous projects on chat. I can do the task for you.
Looking FOorward
Relevant Skills and Experience
VBA
Proposed Milestones
£50 GBP - Complete Excel file