MeredithUW
New Member
- Joined
- Dec 5, 2013
- Messages
- 4
I've been looking for a solution with no success. My workbook has been moved to Google Spreadsheets but for the most part, this should be a non-issue. I created a Google Form that automatically feeds results/data to a tab in my spreadsheet. Each "survey" completed fills a row on the sheet so that the different answer to the same questions are lined up in the same columns. I have created analysis report tabs that populate using the information pulled from the results/data tab "Form Responses 2". Most of my cells in the reports use a formula like this:
=ARRAYFORMULA(INDEX('Form Responses 2'!$A$2:$L$104;SMALL(IF('Form Responses 2'!$D$2:$D$105=A$2;ROW('Form Responses 2'!$A$2:$L$105)-1);ROW($A9)),5))
We are tracking advocacy with legislators. "A$2" is a legislator and we pull data from different columns (activity notes, date of entry, advocate the notes are about, and the person who entered it) in the same row the legislator is identified during the survey entry. Easy peasy. But now, I'm over my head. I tried using the same formula to pull information about advocates in the same way, from the same spreadsheet and array ('Form Responses 2'!A2:L104). But we don't want duplicates of the advocates. It will be common for advocates to be listed in multiple survey entries but we don't want them to be listed multiple times in a report showing a list of advocates by advocacy type. The Form lists all entries with the advocate's names (Column E), advocacy type (business leader, educator, parent, etc.) (G), and engagement level (Identified, Prepared, Engaged) (I) by Legislator (D). From this information we want to only list the advocate once per legislator. The "Legislator Report" tab has a grid that lists Advocacy Types in Column A and I want the report to auto-fill in the names of the advocates horizontally next to their advocacy type for that legislator. The legislator's name is in C5 in a data validation drop down list.
The other formula I came across is:
=ARRAYFORMULA(IFERROR(INDEX('Form Responses 2'!$E$2:$E$104,MATCH(0,INDEX(COUNTIF($A$2:$A2,'Form Responses 2'!$E$2:$E$104),0),0)),""))
This works great with not pulling duplicates (when copied/pasted vertically) but I don't know how to combine the two formulas or whatever I need to do so that its only pulling the names without duplicates for the legislator listed C5 and the advocacy type ($A1).
Hopefully this is enough information to help me.
=ARRAYFORMULA(INDEX('Form Responses 2'!$A$2:$L$104;SMALL(IF('Form Responses 2'!$D$2:$D$105=A$2;ROW('Form Responses 2'!$A$2:$L$105)-1);ROW($A9)),5))
We are tracking advocacy with legislators. "A$2" is a legislator and we pull data from different columns (activity notes, date of entry, advocate the notes are about, and the person who entered it) in the same row the legislator is identified during the survey entry. Easy peasy. But now, I'm over my head. I tried using the same formula to pull information about advocates in the same way, from the same spreadsheet and array ('Form Responses 2'!A2:L104). But we don't want duplicates of the advocates. It will be common for advocates to be listed in multiple survey entries but we don't want them to be listed multiple times in a report showing a list of advocates by advocacy type. The Form lists all entries with the advocate's names (Column E), advocacy type (business leader, educator, parent, etc.) (G), and engagement level (Identified, Prepared, Engaged) (I) by Legislator (D). From this information we want to only list the advocate once per legislator. The "Legislator Report" tab has a grid that lists Advocacy Types in Column A and I want the report to auto-fill in the names of the advocates horizontally next to their advocacy type for that legislator. The legislator's name is in C5 in a data validation drop down list.
The other formula I came across is:
=ARRAYFORMULA(IFERROR(INDEX('Form Responses 2'!$E$2:$E$104,MATCH(0,INDEX(COUNTIF($A$2:$A2,'Form Responses 2'!$E$2:$E$104),0),0)),""))
This works great with not pulling duplicates (when copied/pasted vertically) but I don't know how to combine the two formulas or whatever I need to do so that its only pulling the names without duplicates for the legislator listed C5 and the advocacy type ($A1).
Hopefully this is enough information to help me.