I would like to hire an Excel Expert
$30-250 AUD
Paid on delivery
I am trying to automate an excel file for reporting purpose. Currently I do it manually and bits and pieces of automation which I have recorded macro, it was working fine till I had to do conditional formatting for duplicates and delete column 1 data based on duplicates.
I have attached a dummy data file for your reference, when I receive the raw data, it consists of 131 columns, I recorded macro to delete unwanted columns which I don’t need for reporting.
Columns in Yellow to keep
Columns in Grey to delete
Here are the steps, I need to perform every week on this workbook.
1. Copy and paste the Raw data worksheet from H:\ drive into macro enabled worksheet
2. Sort column 1 by A-Z
3. Use conditional formatting and highlight the duplicates in column 2 (pink color)
4. Once conditional formatting highlighted duplicates, filter them by cell color (Pink)
5. Highlight the rows in column 1 whose value = “capture & form” Delete these rows only if they are duplicates (these will be highlighted in pink color on column 2). Duplicate captures and forms are deleted in this step.
6. Delete all the columns which are highlighted in Grey, leaving only columns in Yellow.
7. Cut (Ctrl X) the left-over “capture, form and blank cells” in column 1 and paste them in sheet 2 (NFA – sheet name). This is where the macro recording doesn’t work.
8. Export/save this workbook with 2 worksheets to (closed folder in H:/drive – when automated), currently manually open the folder and paste the workbook with file name: data &Current date.
9. This is where the fun part starts: this task takes 2 to 3 days in between other jobs.
10. Column header (65 Question 20), select filter and for every item in the filter (e.g.: sort by value “A”, copy the filtered selection rows and paste in new workbook by the name “A and date” in H:\ drive folder, repeat this process for all the items in filter and save them, this could be the closed workbook for automation purpose, currently manually opened and pasted the workbook.
11. Repeat the step 10 for Column Headers (68 Question 23), (63 Question 18) and (60 Question 15) in next blank cell.
12. In the above steps copy all the “A” from column 65 Question 20 and “A” from column 68 Question 23 in next available blank cell, repeat the same process for Column Headers (68 Question 23), (63 Question 18) and (60 Question 15) in next blank cell.
As you can see there is lot of manual work involved, I tried to automate bits and pieces, don’t have skills to automate whole process, trying to learn VBA.
Forgot to mention, I have a Userform with buttons (this is what I used for recorded macro to sort, delete unwanted columns), this Userform is on the workbook where I copy and past the raw data sheet and work on it.
If you can help with automation, it will save lot of time.
Thanks in advance.
Project ID: #17657139
About the project
Awarded to:
13 freelancers are bidding on average $138 for this job
Hello Sir Im excel VBA macro and formula Expert :) i can do make your excel spreadsheet automaticly Sir :) please give me this job and im able to start working today Thank You Sincerely Galih AP
Hello there As an excel and VBA expert, i can develop the necessary VBA code for your automation process. Please contact me and let us discuss more on your project. Thanks
Hi, Experienced Data Analyst and Macro Developer. I have read your instructions carefully. I have excellent knowledge and experience of creating User Form, Command Button, Drop Down, Radio Buttons, List Boxes More
Hi, I am fast and Extremely Detailed Oriented. I can do a great work for you and ensure your complete Satisfaction. I understand the value of deadline. if you are not satisfied You do not need to pay a penny. Thank You More