Hi,
I normally use vscript (macros) and I wanted to create a script to pick up the value on fields which are shaded in colour in Excel.
Background: I sent out a survey in Excel to a number of people. Response came back with various fields being shaded (eg Agree, Disagree, etc). These have values of 1-5 in them. However, I need to assess the value provided on each question as a whole, hence, having them shaded doesnt help.
This brought me to the question of: How can I use IDEA to identify the value in each of the questions that was shaded in colour?
Pls do let me know what your experiences are in these scenarios. Thank you!
ericardo79
Offline
Last seen: 4 years 7 months ago
Joined: 03/07/2019 - 21:58
Try out this macro in the Excel file containing your data. Before executing the macro, please set a few variables such as maximum rows, column number with colors and where to insert the new column.
Sub Macro1()
max_rows = 9
ans_col = 2
new_col = 3
For i = 2 To max_rows
Cells(i, new_col).Value = Cells(i, ans_col).DisplayFormat.Interior.Color
Next
End Sub
Based on the color values, you could arrive at the required responses such as Agree, Disagree etc, either by using a VLOOKUP or by creating a nested IF formula in Excel. Then, just import into IDEA.
Note: An Excel file with a macro has to be saved as .xlsm file. So, you just execute this macro and save the file as .xlsx file to be imported in IDEA
Another option is to use a Python script.