So I have an excel sheet with a bunch of responses to a survey, with each question as a heading of a column with the first column being the date the survey was taken and the second column being the time. Each respondent's answers are a row and the very first row of the sheet is blank. So column 5, row 8 is the answer to the answer to the 3rd question for the 6th respondent. The answers are pre-selected, as in, a respondent can only CHOOSE one of a few answers, sometimes more than one (which is where my trouble is).
I'm making a summary report, with the count of each response totalled by date. So my summary report has unique dates in the first column (which is column "V") with possible responses to each question as a heading. So if question 1's possible responses are "Red", "Blue" or "Green", those each appear as a column heading.
I'm using a countifs function to first check the date of the response in column A against a list of unique dates in column V . It then checks to see if the column heading matches the appropriate column.
In some instances, respondents could answer multiple answers for one question, though. And the responses appear in this format:
["Red", "Blue", "Green"] or ["Red"] or ["Blue", "Green"]
So this code works fine when I just want to see if the the column heading (which is in row 2) matches:
but I need to see if a cell CONTAINS the column heading:
This doesn't work. Is there a way to incorporate wild cards here?
I'm making a summary report, with the count of each response totalled by date. So my summary report has unique dates in the first column (which is column "V") with possible responses to each question as a heading. So if question 1's possible responses are "Red", "Blue" or "Green", those each appear as a column heading.
I'm using a countifs function to first check the date of the response in column A against a list of unique dates in column V . It then checks to see if the column heading matches the appropriate column.
In some instances, respondents could answer multiple answers for one question, though. And the responses appear in this format:
["Red", "Blue", "Green"] or ["Red"] or ["Blue", "Green"]
So this code works fine when I just want to see if the the column heading (which is in row 2) matches:
Code:
Range("AE3:AR" & Cells(Rows.Count, "V").End(xlUp).Row).Formula = "=COUNTIFS(C1,RC22,C7,R2C)"but I need to see if a cell CONTAINS the column heading:
Code:
Range("AT3:BB" & Cells(Rows.Count, "V").End(xlUp).Row).Formula = "=COUNTIFS(C1,RC22,C7," * "&R2C&" * ")"This doesn't work. Is there a way to incorporate wild cards here?
