Hi,
I'm in need of help from you guys. Below is the Project Description for which I'm stuck and in need of help.
Need to create a Macro/VBA in an Excel 2013 workbook, that gets data from another Excel 2013 workbook. Every week I get an Excel spreadsheet with results from a Survey that customers are taking. I want to import this Survey data into another Excel workbook, and put it into a usable format as described below and provided in samples (with fake data).
All data comes from an Excel 2013 workbook named “Source.xlsx” (sample provided, fake data). The data and content change in this file, but the column names and format remain the same. The number of rows will be 100-500.
When the Status.xlsx workbook is opened, the macro will automatically run and overwrite all previous data.
The Status.xlsx workbook will have 3 Sheets (sample provided, fake data). To get the data formatted as I want it, I have outlined what I believe to be the steps for each Sheet:
Sheet #1
1. Get data from Source.xlsx workbook. Insert data into the Source.xlsx workbook, Sheet 1 in this order (only these columns are needed): Account Manager, Company Name, First Name, Last Name, Email Address, Survey Status.
2. Rename data in the Survey Status column as follows:
a. “Named complete” to “SURVEY COMPLETED!”
b. “Not yet accessed link” to “Has NOT started Survey”
c. “Suspended” to “Started Survey but didn't finish”
3. Size columns to fit.
4. Sort on Account Manager (ascending), Company Name (ascending), Survey Status (ascending).
5. Apply Conditional Formatting Rules on the “Survey Status” Column and use these “fill” color:
a. Started Survey but didn't finish = blue
b. Email Hard Bounceback = red
c. Suppressed during fieldwork = red
d. Has NOT started Survey = Yellow
e. SURVEY COMPLETED!= Green
f. Everything else = (no coloring)
6. Turn on Filter for all columns, but do not select any
Sheet #2
Get data from Sheet 1 (or from Source.xlsx) and summarize so it looks like the example. Here is the description:
1. Column A is Account Manager (but no title)
2. Column B is “Completed %” (# Completed / #Submitted) expressed as a percentage (for this row)
3. Column C is “# Submitted” (total count of all rows in Source.xlsx workbook or sheet 1 that have this Account manager)
4. Column D is “# Completed” ” (total of all rows in Source workbook or sheet 1 for this Account Manager that have the “Survey Status” of “SURVEY COMPLETED!”)
5. Column E is # Outstanding” (Difference of Submitted column – Completed column for this row)
6. Sort the table by the Completed % column
7. Subtotal on the bottom of #Submitted, #Completed, # Outstanding columns
Sheet 3 (Pie chart)
Two Slices
Slice 1 is the subtotal number from the “# Completed” column from sheet 2, and is colored Green, with the text in the legend saying “#Completed” (where it currently says “1”)
Slice 2 is the subtotal number from the “#Outstanding” column from sheet 2 and is colored Red, with the text in the legend saying “# Outstanding” (where it currently says “2”)
END.
Guys this is urgent. Please help me on this.
Thanks
Bookmarks