I want to analyze a survey which I made. Below you see an example of how I structured the answers.
Now I would like to rank the answes (rows 2-6) in a list according to often each item was mentioned.
I could do transpose the data manually and delete items which were mentioned various times. However, since it was a pretty big survey, it would take to long. Is there a formula to do it?
Original:
Person A
Person B
Person C
Water
Water
Sugar
Washing
Washing
Pasta
Boxes
Milk
Water
Frozen
Vegs
Fish
Cleaning
Cheese
Water
Example of how it should look like:
Water
3 (times mentioned)
100% (because everone mentioned it)
After survey questions and responses are in excel, how do you analyze this data using a correlation matrix and regression analysis for reporting or testing hypotheses?
I have a survey/form that was created in excel, and that is answered in excel. I am required to extract the data into a worksheet for each response received. I am able to pull the text that is entered, however i am having trouble pulling through the info from the checkboxes. So if a checkbox is ticked i cannot pull that through?
I did a survey of what kind of clothes people like to wear and why. So there are two things that people have to write down. ex) jeans and cus they're comfortable / shirts and cus they look good
The data that I received was organized as followed
I just put in random things just so you can get the idea of whats what
Id jeans shirts cardigans
[Code].....
Okay so what I want to know is how I can get the responses from the data spreadsheet I showed you before into this organized data table above.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
I created an excel workbook with multiple cell reference throughout the various worksheets. I did this because I was creating a dependent drop down list. When I try to sort my data alphabetically, all my cell name references get changed. How I can sort alphabetically without losing my cell name references?
Column 3 is just a ranking from 1-30. So I select all the data in columns 1 and 2 and hit sort from largest to smallest and it looks like excel computes, but nothing changes and it doesn't sort from largest to smallest? A
The first part of this code is fine and completes all the borders. The second part is to the all the workbook by Data Sort in ascending order starting with
Column C Then Q Then column V
But also knowing to search to the last line. I think i might be close but not close enough for this to work.
Sub Macro2() Dim LR As Long, i As Integer Dim mysheet As Worksheet
Sheets("Test").Select LR = Range("A" & Rows.Count).End(xlUp).Row With Range("A9:AD" & LR)
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings() Dim iCTR As Integer Dim yCTR As Integer Dim zCTR As Integer
zCTR = 11 For iCTR = 12 To 23 For yCTR = 1 To 10 If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value zCTR = zCTR + 1 End If Next yCTR Next iCTR Range("AA11:AA" & zCTR).Select Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
I followed a youtube video on setting up a survey in excel, and it works great, but it was only for two options. I wanted to make it 5 options per question, but when I started adding to the code ( which I thought was correct, but was not ) I kept getting errors. It probably is a simple addition. How to make the survey options more than two?
There are three tabs. The first tab (Start) only has a button that leads to the QA Survey. The Second tab has the questions and answers. The third tab as three columns in it as well for the name of the person, question number, and answer choice.
Here is the code inside the form (QASurvey):
Code: Private Sub button_next_Click() ''confirm there is a name If TextBox1.Value = "" Then MsgBox ("Please enter your name") [Code] ...
Here is the code in Module 1:
Code: ''global variables Public info() As Variant Public results() As Variant Public questionnumber As Integer
Here is the code on the sheet that has the button to start the Survey Form:
Code: Private Sub Start_Button_Click() QASurvey.Show End Sub
I am trying to rank questions within a survey with excel calculations and not VBA. The rank would take place for each individual section and would provide the top three right questions and top three wrong questions. The example below explains the ranking.
Brief facts: Each survey has up to 30 sections.
Each section has up to 150 questions.
Each question has none or no limt of sub-questions.(usually between 1 and 20, weekly changes require this number to change)
The section's number of questions change weekly with updates.
This week section 2 begins with #12, next week begins with #16 because more questions were added to section 1.
There are three possible choices to answer a question (yes, no, or n/a.)
Sub-questions are checkboxes of explanations on why the question could be answered wrong.
Each question may have 0-10 points assigned.
Each sub-question may have 0-10 points assigned.
When a question is answered "no" is when a sub-question may be checked to help explain why.
A question with sub-questions may only be assigned points at the sub-question level. The sub-questions have the points and do not sum up to total the question value. When a question is answered "no" and a sub-question is checked, the points received are 0 points for that sub-question. Unchecked sub-questions receive the full amount of points.
When a question is answered "yes" all points are received.
The Rank:
The rank would take place for each individual section and would provide the top three right questions and top three wrong questions at the question level. My example below has more than three to help explain further.
Select the highest actual score of question or sub-question. Start with 10 points to 0 points.
When a match is found that becomes the first "correct" question rank. #17 is 1 since 17.1 had an 8 point sub-question.
When there is a tie #14,15,18, the tie breaker is the sum of correct sub-questions added to the question score. #14 = 7 points.
The second sort is the # in ascending order #15 and then 18.
The hardest part of the rank is the flexibility of a changing survey with question/sub-question deletions and additions in each section. ...
I'm creating a spreadsheet to collect survey data and how I have it set up is this:
Question 1 is a simple yes/no/maybe question - it uses an ActiveX spin button that the user presses up/down to increase/decrease the total. It looks like this:
Private Sub Yes_SpinUp() With Range("C3") .Value = WorksheetFunction.Min(15000, .Value + 1) End With End Sub
Private Sub Yes_SpinDown() With Range("C3") .Value = WorksheetFunction.Max(0, .Value - 1) End With End Sub
Cell C3 is where I keep track of how many people answered yes
The button is called "yes" under the button properties
So basically, I have one privatesub for spinning the value up and down. It works fine and is easily copy-paste-able for other answers, it just takes up a lot of space. I'm wondering if there's code I can write to handle the up/down in one sub. If not, i'll stick with what I have.
I have a list of vehicle number plates (or License plates for some of you) that have been recorded on a video camera and then converted to a text file using recognition software.
Unfortunately the records are far from 100% accurate so any data manipulation (travel times between points etc) becomes a bit dodgy and clumbsy to allow for this. I want to keep all of the data entries but need to identify each vehicle with a little more accuracy. I'm not worried about whether the plate number recorded is correct, only that it is the same for each instance that a particular vehicle is recorded.
The data comes in looking roughly like this:
ABC123 DEF456 AC13 A123 DE56 DEF56
I would like it to look like this:
ABC123 DEF456 ABC123 ABC123 DEF456 DEF456
Or even this:
AB123 DF45 AB123 AB123 DF45 DF45
It doesn't matter which of the above or any other compination as long as there are at least 4 characters.
The surveys are taken over up to a week so there are generally about 5000 records for each time slot that need to be processed.
I have tried to modify the Fuzzy matching functions with no success.
I have just recently finished inputting data into a spreadsheet which is a whole bunch of survey responses and I am trying to figure out the best way of now “analysing” it. Unfortunately the questionnaire consists of various types of questions, including some open ended, some where the participant selected a number on a scale, or the participants could select any number of options (e.g. tick any that apply).
From a brief search, I see that you can’t have two headings as such for a pivot table so I am wondering what the best approach might be. I have attached a sample spreadsheet. I have a hunch though that it’s going to be a matter of analysing each question individually and using filters and countif formulas (see attached).
An offset sumproduct series of functions provides me the total score percentage for the top level categories. There are 5-6 in total and can easily be charted in a radar.
The second step is to maintain the 'dynamic aspect' (being able to delete rows) and lookup the high-level category, and the subsection and return the score for the individual question. This way I can create a chart for each of the 5 top level categories and show the survey score for each answer in the category. Note each question has a maximum score of 5 and are scored 1-5.
I have a few errors looking up values and percentages coming back as 500% with the addition of new columns (in red in attached). The first sheet is error free, the second sheet is the 'build'.
I have recorded the data from 12 records (i actually have hundreds, but im just using a small sample). Each record is represented by a vertical column. I want to find the total number of "x's" for only the records from Country1 (from all the answers). Then I want to find the number of "x's" from only Country2. I have tried the "countifs" function but cannot find a way to make it work. Would a different array function be better? How would I write it?
P.S. The answer for Country1 should be: 15. The answer for Country2 should be: 5.
I have a spreadsheet which has a list of properties with a list of survey dates. The complication is that every property has multiple surveys and these are all on separate lines with the spreadsheet. The number of surveys could also be different depending on the property in question.
What I would like to do is to compile a report which only shows me when the latest particular type of survey (there are five types of survey, I'll call them 1, 2, 3, 4 and 5).
The survey type is shown in column C, the property is shown in column A and the survey date is shown in column F.
I conducted a survey and want to tabulate the results in excel. There is a lot of paper, so I want to list each answer on the spreadsheet and have a button next to it to tabulate the responses instead of doing it by hand. When I look at each survey, I want to click the button for the corresponding answer. I want to have excel tally/increment each response each type I click the button.
So what macro do I need to assign to each button in order to do this? Or can I just click on a cell and have it increment? Or what can I do to achieve this?
My attempts have given my circular reference errors, or if I do the iteration thing (tools, options, iterations), excel increments EVERYTHING in the spreadsheet by one instead of just one specific question.
I am trying to come up with a good way to average a particular question in a survey result for instructors, however the caveat has me pretty puzzled. We're looking to be able to filter by mm/dd/yyyy date (which pulls mm/dd/yyyy hh:mm from raw data) and then have one average of all scores for each instructor listed. It gets a bit complicated in that the actual survey has 6 drop down boxes with all the instructors names, so one instructor could have data for the same (or any) date in any of the 6 colored columns (name, score, qualitative x 6).
My initial thought was to list out the names on a second sheet and then have some sort of averageifs to combine the columns, but I am a bit lost on how to take the dates into account, let alone how to sum the columns then average.
I have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.
I have several long lists (each is 600+) of vocabulary words & definitions and need to alphabetize/sort the words, but need to keep the definitions w/ the words.
A1 = word 1 A2 = def. 1 A3 = word 2 A4 = def. 2 A5 = word 3 A6 = def. 3
Standard sort will alphabetize all rows & the words will be separated from the definitions. I could group rows 1 & 2 together, rows 3 & 4 together, etc. but that's clunky & time-consuming.
On the intranet we have some reports in PDF file when I open it in excel i get this kind of results: from pdf opened in excel.xlsx
Now I want to sort values in that way to operate, calculate and to be more organized.
Let me explain what does mean each parts of values in report from intranet..
Red text in A column = type of event in C column = start of event in D column = end of event in E column = duration of event in F column = control number Blue text in H column = material name
Green text in B column -> if in A column text is green and write "Komentar:" then in B Column is comment typed by employees, If in A column text is green and write "Pri dogodku:" then in B column is type of event where was comment above writen if in A column text is green and write "Vnesel:" then in B column is name of employees wich write comment
The name of line where that event has been is in D column if in A column is date with black text (hope u understand see in attachment)
So I want to paste this report to one sheet and have in other sheet report in order A column is DATE, B LINE, C TYPE OF EVENT, D EMPLOYEES WHO WRITE COMMENT, F COMMENT, G DURATION OF EVENT, H CONTROL NUMBER, I MATERIAL NAME
In each row is details of event so I could see how many event I have, which kind of, duration etc
I have a macro that should be sorting the last column of data, but it gives me an error code of "400". No explanation of the error, just the number. I have a feeling it is because I have formulas in place to pull the data from other sources, and the associated "#Value!" error is whited out by using the conditional formatting. Is there a way to have this macro only look at numeric values?
--------BEGIN VBA-------- Sub SortLastColumn() Dim myRange As Range, myCol As Integer, sortCol As String
I am looking for some to create a simple macro to sort multiple rows in ascending order based on the values in a particular column in the selected range. I want to sort A4:AI1004 in ascending order by column D. I recorded the following macro in Excel 2010:
VB:
Sub sort() ' ' sort Macro ' sort by column D (Sequence #) ' ' Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select
[Code]...
First of all, I think this code can probably be simplified. Secondly, it does not work in older versions of Excel. In 2007 it always ends in a runtime error. How can I clean this up so that it will work in both versions of Excel?
