Creating a filtered sheet based on a previous sheet.

Hello!

I am attempting to create a spreadsheet that essentially copies several columns (A,B,C,Q,AG,BC,BD,BE,BF,BG,BH) from every row from another sheet that contains a specific value. The source spreadsheet is about 1500 rows deep and 63 columns wide. I need to copy every row where the value in column AG is "Spoke directly to a representitive", and none of columns BC-BH are blank. If neither of those two filter values are met then I want the line skipped completely.

I know how to do it using an array (mostly). I've worked out the below formula to return all the values I need for column C (and adding extra clauses to filter out the blanks in BC-BH). I was then going to use offsets to do it again for each of the columns I need. The issue is that just this partial formula for ONE of the 11 columns I need to generate takes about 2 minutes to process and slows the sheet down to a crawl. I can't imagine trying to implement this across the entire workbook (I have about 5 more sheets in the workbook that need similar filtering. I've done some research, and it seems that a Helper column is what I need to make the calculations more efficient, but can't for the life of me figure it out. Can anyone help?

Thanks!

Formula:

Please Login or Register  to view this content.

*the misspelling of representative is in the source, I am exporting from, so it needs to stay misspelled.

It would help immensely to have a small sample workbook to look at and experiment with. Would it be possible to post one (use: Go Advanced --> Manage Attachments) with all of your headers and columns and maybe... 10 or so rows? Be sure to alter or remove any sensitive data.