+ Reply to Thread
Results 1 to 2 of 2

Excel Macro to import Survey data as well as sort & format

  1. #1
    Registered User
    Join Date
    11-10-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    13

    Question Excel Macro to import Survey data as well as sort & format

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-10-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Excel Macro to import Survey data as well as sort & format

    Guys please help....depending on u guys...plzzzzzz !!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to import data from different work book and sort
    By jerry499 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-11-2012, 08:34 PM
  2. Replies: 0
    Last Post: 10-19-2011, 10:17 PM
  3. Need a macro to import and format data.
    By dcgrove in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2009, 06:01 PM
  4. import text and sort data into excel
    By Boozman in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] import text and sort data into excel
    By Boozman in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1