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

vincemattam

New Member
Joined
Nov 23, 2022
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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
 
Last edited by a moderator:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

Raw Data-Col A-G.JPG
Raw Data-Col Sec1-Sec9_Question Sample.JPG
Raw Data-Col Sec9_Sample.JPG
Raw Data-Col Sec10a_Sample.JPG
Raw Data-Col Sec11a_Sample.JPG
Raw Data-Col Sec12a_Sample.JPG
Raw Data-Col Sec13a to Sec 15c_Sample.JPG
Agenda Sheet-p1.JPG
Agenda Sheet-p2.JPG
Agenda Sheet-p3.JPG
Agenda Sheet-p4.JPG
Agenda Sheet-p5.JPG
 
Upvote 0
@mumps, Sorry I had to post it here as an admin wanted me to do so. The other thread is closed as of now.

Regards,
VM
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top