Filter

[sksanjeev786]

Hi Team,

I have 100+ questions for each Excel sheet and I need to filter questions that start with "(OP19) CALL TO ACTION" only among 100 Questions.

Can anyone help me with this

Regards,
Sanjeev

 

[KRice]

You're welcome...I'm happy to help.

 

[sksanjeev786]

You could add an autofilter to the column and simply filter to show those that begin with "(OP19) CALL TO ACTION". Alternatively, you could use the FILTER function to construct a new list beginning with the specified text:

Book1
	A	B	C	D	E
1	(OP19) CALL TO ACTION		Items beginning with specified text
2	(OP19) CALL TO ACTION - Speak To Friends And Family About What Was Advertised		(OP19) CALL TO ACTION
3	(OP19) CALL TO ACTION - Visit The Website/Call The Phone Number		(OP19) CALL TO ACTION - Speak To Friends And Family About What Was Advertised
4	(OP19) CALL TO ACTION - Find Out More About What Was Advertised		(OP19) CALL TO ACTION - Visit The Website/Call The Phone Number
5	(OP19) CALL TO ACTION - Upgrade My Internet Speed With Cox		(OP19) CALL TO ACTION - Find Out More About What Was Advertised
6	(OP19) CALL TO ACTION - Add A New Product Or Service With Cox		(OP19) CALL TO ACTION - Upgrade My Internet Speed With Cox
7	(OP19) CALL TO ACTION - Switch From My Current Provider		(OP19) CALL TO ACTION - Add A New Product Or Service With Cox
8	(OP19) CALL TO ACTION - Make A Connection With A Friend, Family Member Or Maybe Someone New		(OP19) CALL TO ACTION - Switch From My Current Provider
9	(OP19) CALL TO ACTION - Talk To A Friend Or Family Member		(OP19) CALL TO ACTION - Make A Connection With A Friend, Family Member Or Maybe Someone New
10	(OP19) CALL TO ACTION - Reach Out To Someone I Haven’t Talked To In A While		(OP19) CALL TO ACTION - Talk To A Friend Or Family Member
11	(OP19) CALL TO ACTION - Do Something Good In My Community For Someone Else		(OP19) CALL TO ACTION - Reach Out To Someone I Haven’t Talked To In A While
12	(OP19) CALL TO ACTION - TOP BOX		(OP19) CALL TO ACTION - Do Something Good In My Community For Someone Else
13	(OP19) CALL TO ACTION - TOP 2 BOX		(OP19) CALL TO ACTION - TOP BOX
14	(OP29) UNIQUENESS OF IMPRESSIONS		(OP19) CALL TO ACTION - TOP 2 BOX
15	(OP22) BRAND FOCUS (INVOLVED SCALE)
16	(OP36) MESSAGE CHECK
17	(OP36) MESSAGE CHECK - Cox Helps Connect Patients With Family Members They Are Unable To Be With In Person.
18	(OP36) MESSAGE CHECK - Cox Brings People Together With Technology
19	(OP36) MESSAGE CHECK - Cox Understands What Is Important To Me And My Family
20	(OP36) MESSAGE CHECK - I Feel Inspired To Connect With My Loved Ones At This Time
21	(OP36) MESSAGE CHECK - Cox Enables Me To Keep Connected With My Family And Friends
22	(OP36) MESSAGE CHECK - TOP BOX
Sheet2

Cell Formulas
Range	Formula
C2:C14	C2	=FILTER(A1:A1000,IFERROR(SEARCH("(OP19) CALL TO ACTION",A1:A1000)=1,0))
Dynamic array formulas.

Hi KRice,

I was trying yesterday to pull the data with Indirect function but unable to pull the data below Formula i have used

=FILTER(INDIRECT("'"&$E$1&"'!A1:A1000"),IFERROR(SEARCH("(OP19) CALL TO ACTION",A1:A1000)=1,0))

 

[KRice]

Why do you want to use the INDIRECT function? Do you know the sheet name?...or are there multiple worksheets now? What is found in cell E1? I am guessing that it is the worksheet name? Is the worksheet named in cell E1 found in the same workbook as this formula?

If INDIRECT is really needed, then you must also describe the filtering criteria correctly...the criteria must relate to the range that you want to filter...like this:

=FILTER(INDIRECT("'"&$E$1&"'!A1:A1000"),IFERROR(SEARCH("(OP19) CALL TO ACTION",INDIRECT("'"&$E$1&"'!A1:A1000"))=1,0))

 

[sksanjeev786]

Why do you want to use the INDIRECT function? Do you know the sheet name?...or are there multiple worksheets now? What is found in cell E1? I am guessing that it is the worksheet name? Is the worksheet named in cell E1 found in the same workbook as this formula?

If INDIRECT is really needed, then you must also describe the filtering criteria correctly...the criteria must relate to the range that you want to filter...like this:

=FILTER(INDIRECT("'"&$E$1&"'!A1:A1000"),IFERROR(SEARCH("(OP19) CALL TO ACTION",INDIRECT("'"&$E$1&"'!A1:A1000"))=1,0))

Hi KRice,

Thank you so much for your help on this

Yes, i have 100+Sheet names, and each sheet has multiple questions mentioned above. so with the help of Indirect, i can collect the data in one go instead of Drop down button.

 

[KRice]

Thanks…I apologize for asking about the multiple sheets. I didn’t review the earlier posts and had forgotten about the original details you provided.

With INDIRECT you will still need to cycle through each sheet name to get the results for each and then compile those into a single list (I thought that is what you want). Have you tried my Power Query suggestion? That will automatically work on all worksheets and create a single list and show the worksheet name for each item.

 

[sksanjeev786]

Thanks…I apologize for asking about the multiple sheets. I didn’t review the earlier posts and had forgotten about the original details you provided.

With INDIRECT you will still need to cycle through each sheet name to get the results for each and then compile those into a single list (I thought that is what you want). Have you tried my Power Query suggestion? That will automatically work on all worksheets and create a single list and show the worksheet name for each item.

Thanks for checking on this Krice.

I have not checked with Power query i want to know the process of how to run via Power query.

 

[KRice]

1. Download this file and place it in the folder with your source workbook (the workbook with 100+ worksheets).
   
   

   MrExcel_20220908_call_result.xlsx

   Shared with Dropbox

   www.dropbox.com
   This file contains the M Code I posted earlier.
2. In your source workbook with all of the worksheets that need to be processed, create a new worksheet, and in cell A1, enter the following:

   =SUBSTITUTE(LEFT(CELL("filename",A2),SEARCH("]",CELL("filename",A2))-1),"[","")

   This formula internally returns the full path to your file, including the file and sheet name...and then it performs some clean up to extract just the file path and file name.
3. Right click on the result and copy it to your clipboard.
4. Open the downloaded file and click on Data > Queries & Connections. You should see a side window open to the right showing the OP19all query (that's what I named query posted earlier).
5. Right click on the OP19all query name and a new popup window should open. Click on Edit at the bottom of that window to open the Power Query (PQ) editor.
6. In the PQ editor, click on View > Advanced Editor to open the editor window for manually editing code.
7. In the first line of code for Source, edit my file path/name by pasting your file path/name (from your clipboard). You should see your full path and file name displayed between quotation marks. Click Done to save this change and exit the editing window. This will place you in the main interface for PQ with the program steps shown sequentially to the right under the section "Applied Steps". You can click on each of these steps to follow the transformation of your data. If you click on the last step (called Filtered Rows), you will see the table that will be returned back to your workbook.
8. Assuming you have no errors to resolve, exit PQ by clicking on File > Close and Load and the table produced by PQ will be loaded back to your Excel workbook.
9. Now anytime new worksheets are added to your source workbook or edits are made to the existing worksheets, you can update the consolidated table by right clicking on the consolidated table and choosing "Refresh" (or use the top level menus and click Data > Refresh All > Refresh All).

 

[sksanjeev786]

1. Download this file and place it in the folder with your source workbook (the workbook with 100+ worksheets).
   
   

   MrExcel_20220908_call_result.xlsx

   Shared with Dropbox

   www.dropbox.com
   This file contains the M Code I posted earlier.
2. In your source workbook with all of the worksheets that need to be processed, create a new worksheet, and in cell A1, enter the following:

   =SUBSTITUTE(LEFT(CELL("filename",A2),SEARCH("]",CELL("filename",A2))-1),"[","")

   This formula internally returns the full path to your file, including the file and sheet name...and then it performs some clean up to extract just the file path and file name.
3. Right click on the result and copy it to your clipboard.
4. Open the downloaded file and click on Data > Queries & Connections. You should see a side window open to the right showing the OP19all query (that's what I named query posted earlier).
5. Right click on the OP19all query name and a new popup window should open. Click on Edit at the bottom of that window to open the Power Query (PQ) editor.
6. In the PQ editor, click on View > Advanced Editor to open the editor window for manually editing code.
7. In the first line of code for Source, edit my file path/name by pasting your file path/name (from your clipboard). You should see your full path and file name displayed between quotation marks. Click Done to save this change and exit the editing window. This will place you in the main interface for PQ with the program steps shown sequentially to the right under the section "Applied Steps". You can click on each of these steps to follow the transformation of your data. If you click on the last step (called Filtered Rows), you will see the table that will be returned back to your workbook.
8. Assuming you have no errors to resolve, exit PQ by clicking on File > Close and Load and the table produced by PQ will be loaded back to your Excel workbook.
9. Now anytime new worksheets are added to your source workbook or edits are made to the existing worksheets, you can update the consolidated table by right clicking on the consolidated table and choosing "Refresh" (or use the top level menus and click Data > Refresh All > Refresh All).

Thank you so so much for your time and hard work on this.

surely i will check and let you know if i have any question on this