Filter

S

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
884
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Could you provide more information? What do you mean by "filter questions"? Do you want to include them...or exclude them? Do you want some information from them, or do you want to do something else with them? Are you using Excel 365 for this (your profile shows two versions). You have multiple sheets: are you interested in doing this separately for each sheet or are you aggregating all questions across all sheets and then doing something with the aggregated list?
 
Upvote 0
KRice said:
Could you provide more information? What do you mean by "filter questions"? Do you want to include them...or exclude them? Do you want some information from them, or do you want to do something else with them? Are you using Excel 365 for this (your profile shows two versions). You have multiple sheets: are you interested in doing this separately for each sheet or are you aggregating all questions across all sheets and then doing something with the aggregated list?
Click to expand...

Hi Krice:)

Thanks for your help on this

Yes i am using office 365 and i need in 1 sheet where ever stubs start from "(OP19) CALL TO ACTION" in Column A only.

Let me know if you need more detaails on this


Regards,
Sanjeev
 
Upvote 0
I don't understand what you mean. Could you please review all of the questions I asked? If possible, post a small working example with the XL2BB add in.
 
Upvote 0
KRice said:
I don't understand what you mean. Could you please review all of the questions I asked? If possible, post a small working example with the XL2BB add in.
Click to expand...

Hi Krice,

Here is the mini excel so i have multiple questions in each sheet so i need only questions which has "(OP19) CALL TO ACTION"

Book1
A
1(OP19) CALL TO ACTION
2(OP19) CALL TO ACTION - Speak To Friends And Family About What Was Advertised
3(OP19) CALL TO ACTION - Visit The Website/Call The Phone Number
4(OP19) CALL TO ACTION - Find Out More About What Was Advertised
5(OP19) CALL TO ACTION - Upgrade My Internet Speed With Cox
6(OP19) CALL TO ACTION - Add A New Product Or Service With Cox
7(OP19) CALL TO ACTION - Switch From My Current Provider
8(OP19) CALL TO ACTION - Make A Connection With A Friend, Family Member Or Maybe Someone New
9(OP19) CALL TO ACTION - Talk To A Friend Or Family Member
10(OP19) CALL TO ACTION - Reach Out To Someone I Haven’t Talked To In A While
11(OP19) CALL TO ACTION - Do Something Good In My Community For Someone Else
12(OP19) CALL TO ACTION - TOP BOX
13(OP19) CALL TO ACTION - TOP 2 BOX
14(OP29) UNIQUENESS OF IMPRESSIONS
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
23(OP36) MESSAGE CHECK - TOP 2 BOX
24(OP40) PROMPTED ATTITUDES
25(OP40) PROMPTED ATTITUDES - I Could Really Relate To The Characters In The Ad
26(OP40) PROMPTED ATTITUDES - Cox Has High-quality Products And Services
27(OP40) PROMPTED ATTITUDES - Cox Is A Smart Choice For The Products And Services I Need
28(OP40) PROMPTED ATTITUDES - Cox Is For People Like Me
29(OP40) PROMPTED ATTITUDES - Cox Offers Innovative Products And Services
30(OP40) PROMPTED ATTITUDES - Cox Understands What I Enjoy And Care About
31(OP40) PROMPTED ATTITUDES - Cox Is A Company I Can Trust
32(OP40) PROMPTED ATTITUDES - Cox Is A Good Value For The Money
33(OP40) PROMPTED ATTITUDES - Cox Is Better Than Other Companies
34(OP40) PROMPTED ATTITUDES - It Reminds Me Of The Good Things About Cox
35(OP40) PROMPTED ATTITUDES - It's My Kind Of Ad
36(OP40) PROMPTED ATTITUDES - Cox Is An Expert In Home Communication And Entertainment Service
37(OP40) PROMPTED ATTITUDES - The Ad Reminds Me Of The Importance Of Real Human Connections
38(OP40) PROMPTED ATTITUDES - TOP BOX
39(OP40) PROMPTED ATTITUDES - TOP 2 BOX
40(OP44) BRAND FIT
41(OPTINA) AGREE TO BE RECORDED
42(OPTINB) AGREE TO BE RECORDED
Sheet1
 
Upvote 0
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
ABCDE
1(OP19) CALL TO ACTIONItems 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
RangeFormula
C2:C14C2=FILTER(A1:A1000,IFERROR(SEARCH("(OP19) CALL TO ACTION",A1:A1000)=1,0))
Dynamic array formulas.
 
Upvote 0
KRice said:
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
ABCDE
1(OP19) CALL TO ACTIONItems 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
RangeFormula
C2:C14C2=FILTER(A1:A1000,IFERROR(SEARCH("(OP19) CALL TO ACTION",A1:A1000)=1,0))
Dynamic array formulas.
Click to expand...
thank you so so much Krice:)
This will save a lot of time and i need output in "Sheet 2" if i add an indirect function initially hope able to get the data.:)
 
Upvote 0
Using INDIRECT is one option. This is why I asked for more details. It might make more sense to use Power Query to create a consolidated list across multiple worksheets and then filter to return what you want.
 
Upvote 0
In just a few steps, you could have Power Query return the list...here is the code I used in a mocked up workbook. I left the worksheets containing the source information in their own workbook. That workbook contains three worksheets-Sheet1, Sheet2, and Sheet3--that had the text you mentioned along with other entries. Then in a separate workbook, I pointed to the source workbook and operated on all of the worksheets to construct a consolidated list, returning the items of interest and the source worksheet name where the information was found. Here is the M-code
Power Query: 
let
    Source = Excel.Workbook(File.Contents("C:\Users\kirkr\...full path to the source file...MrExcel_20220908_call.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1"}, {"Data.Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each Text.StartsWith([Data.Column1], "(OP19) CALL TO ACTION"))
in
    #"Filtered Rows"
Then upon exiting Power Query, I chose "Close & Load To" and loaded the results table back into my workbook, producing this result:
NameData.Column1
Sheet1(OP19) CALL TO ACTION - Do Something Good In My Community For Someone Else
Sheet1(OP19) CALL TO ACTION - TOP BOX
Sheet1(OP19) CALL TO ACTION - Add A New Product Or Service With Cox
Sheet1(OP19) CALL TO ACTION - Reach Out To Someone I Haven’t Talked To In A While
Sheet2(OP19) CALL TO ACTION - Talk To A Friend Or Family Member
Sheet2(OP19) CALL TO ACTION - Reach Out To Someone I Haven’t Talked To In A While
Sheet2(OP19) CALL TO ACTION - Do Something Good In My Community For Someone Else
Sheet3(OP19) CALL TO ACTION - Visit The Website/Call The Phone Number
Sheet3(OP19) CALL TO ACTION - Find Out More About What Was Advertised
Sheet3(OP19) CALL TO ACTION - Upgrade My Internet Speed With Cox
Sheet3(OP19) CALL TO ACTION - Add A New Product Or Service With Cox
 
Upvote 0
Solution
KRice said:
In just a few steps, you could have Power Query return the list...here is the code I used in a mocked up workbook. I left the worksheets containing the source information in their own workbook. That workbook contains three worksheets-Sheet1, Sheet2, and Sheet3--that had the text you mentioned along with other entries. Then in a separate workbook, I pointed to the source workbook and operated on all of the worksheets to construct a consolidated list, returning the items of interest and the source worksheet name where the information was found. Here is the M-code
Power Query: 
let
    Source = Excel.Workbook(File.Contents("C:\Users\kirkr\...full path to the source file...MrExcel_20220908_call.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1"}, {"Data.Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each Text.StartsWith([Data.Column1], "(OP19) CALL TO ACTION"))
in
    #"Filtered Rows"
Then upon exiting Power Query, I chose "Close & Load To" and loaded the results table back into my workbook, producing this result:
NameData.Column1
Sheet1(OP19) CALL TO ACTION - Do Something Good In My Community For Someone Else
Sheet1(OP19) CALL TO ACTION - TOP BOX
Sheet1(OP19) CALL TO ACTION - Add A New Product Or Service With Cox
Sheet1(OP19) CALL TO ACTION - Reach Out To Someone I Haven’t Talked To In A While
Sheet2(OP19) CALL TO ACTION - Talk To A Friend Or Family Member
Sheet2(OP19) CALL TO ACTION - Reach Out To Someone I Haven’t Talked To In A While
Sheet2(OP19) CALL TO ACTION - Do Something Good In My Community For Someone Else
Sheet3(OP19) CALL TO ACTION - Visit The Website/Call The Phone Number
Sheet3(OP19) CALL TO ACTION - Find Out More About What Was Advertised
Sheet3(OP19) CALL TO ACTION - Upgrade My Internet Speed With Cox
Sheet3(OP19) CALL TO ACTION - Add A New Product Or Service With Cox
Click to expand...
thanks so much, Krice for your help and time on this.

This made my day! and appreciate your support and hard work with this:):)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Need to copy Table from File A to to File B (Client PPT)
Replies
0
Views
97
sksanjeev786
S
S
  • Question
need help in the formula
Replies
1
Views
70
bobsan42
bobsan42
S
  • Solved
Need data for similar names
Replies
2
Views
153
sksanjeev786
S
R
  • Solved
Using FILTER with COUNTIF
Replies
2
Views
109
Raddle
R
S
  • Question
Query on Macro
Replies
0
Views
175
sksanjeev786
S

Forum statistics

Threads
1,217,393
Messages
6,136,332
Members
450,005
Latest member
BigPaws

Share this page

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