Request for help - VBA Script to automate data transfer from Survey Raw File to another Excel File in a specific format.

vincemattam · Nov 23, 2022

Hi friends,

First of all, I am not an Excel expert at all.
I have around 25 survey data files (Excel) - each with 6-8 survey respondents. I would like to get all these data transferred to another Excel file (pre-formatted) so that I can get a agenda file for the meeting. I would like make a request to create a VBA script for the same.

I have the following files (not sure how to attach these files) for your review and use.
1. Raw Data from the survey (Excel File)
2. Transposed Data - column data changed as rows; and row data changed as columns (Excel File)
3. Data cell reference mark up sheet - Showing what data goes where (PDF File)
4. Formatted Meeting Agenda Sheet - Showing how the end product should look like (Excel File), It already has a macro (BeforeDoubleClick, credits goes to original creator) in it.

In the PDF markup sheet the cell references are from the Transpose Excel Sheet.
Number of rows starting with 10a, 11a & 12a could vary - 10a could be (max 40); 11a could be (max 30); & 12a could be (max 30).
All descriptive responses should start with an " * "

Any help/input to accomplish this task is greatly appreciated. Thanks to all in advance.

Regards,
VM

vincemattam · Nov 24, 2022

Hi mumps,

I have 25 survey results downloaded as excel files from Jot Form Survey. I need to create a Meeting Agenda Sheet (in a specific format Excel file) from each survey after extracting data from each survey into specific cell locations. I already have a macro (BeforeDoubleClick, credits and thanks to original developer) in the Formated Excel Sheet. For this I would like to get some help to create a VBA script/macro.
Ideally there should be two macro buttons:
First Macro Button (in the formatted Excel file) when clicked should allow me to select the raw data file and enable the data transfer to specific cells. The data extraction is to be done as follows:
Each survey has 3-8 respondents (number varies). The questions of the survey is arranged as column headers.
First 7 columns (A:G) are specific info about project (Project Number, Project Name, Project Location, Division, Project Manager, Name, Designation).
Next set of columns (H:DG) are survey questions (starting as, 1a, 1a, 1b, 1b, ....2a, 2a, ...2d, 2d,...2h, 2h,...............9a, 9a, 9b, 9b,.....9g, 9g) separated into 9 Sections, with varying number of questions in each sections.
Next 3 sections (columns starting with 10a, 11a, 12a) have data from 3 tables the respondents had to fill in from drop down options.
First section (10a) to rate a Enggr, Consultant, Client, Project Coordinator, Inspector, Municipal Officer, Other etc. But not all in one project. It could vary depending on the project. So the number of columns also vary. But all the column headers will start with "10a". A set of 3 continuous column headers contain 10a. Consultants Performance: >> 1 >> Engineer/Inspector Name, 10a. Consultants Performance: >> 1 >> Consultant Company Name, 10a. Consultants Performance: >> 1 >> Rating (for each stake holder in that project). This could be a maximum of 30 (10x3) columns.
Second section (11a) to rate a Supplier. It could also vary depending on the project. So the number of columns for this section also varies. But all the column headers will start with "11a". A set of 4 continuous column headers contain the text 11a. Suppliers Performance: >> 1 >> Supplier Agent Name, 11a. Suppliers Performance: >> 1 >> Supplier Company Name, 11a. Suppliers Performance: >> 1 >> Rating, 11a. Suppliers Performance: >> 1 >> Comments (for each Supplier in that project). This could be a maximum of 40 (10x4) columns.
Third section (12a) to rate a Sub-Contractor. It could also vary depending on the project. So the number of columns for this section also varies. But all the column headers will start with "12a". A set of 4 continuous column headers contain the text 12a. Subcontractors Performance: >> 1 >> Employee Name, 12a. Subcontractors Performance: >> 1 >> Subcontractor Company Name, 12a. Subcontractors Performance: >> 1 >> Rating, 12a. Subcontractors Performance: >> 1 >> Comments (for each Supplier in that project). This could be a maximum of 40 (10x4) columns.
Next 5 Sections the column headers will contain the texts - 13a, 14a, 15a. Lesson 1:, 15a. Lesson 2:, 15a. Lesson 3: have descriptive comments which needs to be concatenated with an "*" at the start of each comment.

Second Macro Button is to create a PDF in a specific name and will be activated only after the project meeting (some editing would happen to the file) is finished.

I appreciate your time and help in this.

Regards,

VM