Need help! multiple criteria array formula (index match etc) but ALSO need to remove duplicates

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.





 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,215,219
Messages
6,123,687
Members
449,117
Latest member
Aaagu

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