I would like to hire an Excel Expert

Completed Posted 5 years ago Paid on delivery
Completed 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.

Excel Excel Macros Excel VBA

Project ID: #17657139

About the project

13 proposals Remote project Active 5 years ago

Awarded to:

$50 AUD in 1 day
(32 Reviews)
5.0

13 freelancers are bidding on average $138 for this job

prameswara

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

$100 AUD in 3 days
(306 Reviews)
6.9
paris2785

Hello! My name's Paris and I was glad to see that you're looking for an Excel developer. You mention that you need somebody to implement the 12 steps through VBA code. I can certainly do that. I've delivered more than More

$200 AUD in 7 days
(139 Reviews)
6.6
satishtl

- Went through the Excel file having the Data Tab (I see about 131 columns) NFa Tab - Can got copied the Raw data - Can get the conditional formattting highlighting a row based on certain condition. - Rem More

$160 AUD in 3 days
(45 Reviews)
6.0
dvcontact

Hi, After reading your project description about automating the formatting of Excel sheet, I feel like you have me in mind when creating the project. I have all it takes to deliver. I can tell you about how highly More

$150 AUD in 3 days
(33 Reviews)
5.5
supper5guy

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

$100 AUD in 3 days
(50 Reviews)
4.9
vsprybhanu

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

$120 AUD in 3 days
(44 Reviews)
4.7
swajantalukdar

I can help you perfectly to automate all the process as needed. let's start....

$200 AUD in 2 days
(9 Reviews)
3.8
Sheraj1983

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

$90 AUD in 3 days
(1 Review)
1.6