Forum Discussion
Working on project that requires me to filter rows blank rows
Perhaps Power Query will do the job, but it's better to discuss on concrete sample.
SergeiBaklan Thank you for the prompt reply. I gave that a shot, but did not have much success. To give more clarity:
I have two spread sheets. Spreadsheet 1 are leads exported from a CRM, and one column (column v) contains a list of names of potential reps that can service this lead. Spreadsheet 2 contains the list of reps that already have this lead. My goal is to filter out the reps that already have these leads so that I may share the leads only with the people who do not have them already.
I tried creating an advanced filter in Power Query, but did not have much success. I manually entered all the reps I have from Spreadsheet 2 into an advanced filter and set as "Or" "Does Not Contain" with the intention of only seeing leads that do not contain a rep that already has the lead.
Edit: I switched the to "And" and now it's filtering properly. Follow up question: There are entries in Spreadsheet 1 that have their column v field blank, and these get filtered out... is there any way to include them in my final results?
Sorry, I didn't catch what do you mean under advance filter in Power Query and what are your steps exactly.
As I see:
- you have raw source data imported from CRM in form of Table or named range
- you query it using From Table/Range connector
- in opened Power Query editor you filter on on of the column column
- you return result back in Excel sheet into the new table
Right. I've done a couple things since my last post so I'll go through my steps:
1. Export leads from CRM and open in Excel.
2. Separate dealers column into 3 columns. When I export the leads, this field is populated as so: "John, Jacob, Jingle" so I separated them into 3 columns "Dealer 1 / Dealer 2 / Dealer 3"
3. I click Data > Get Data > From Table/Range
4. In here, I have 3 queries created.
Query 1: Source > Changed Type > Filtered Rows (This filters dealers out of the row that have received the lead already. To do this, I created a filter for the row as follows: Column: Dealer 1 "does not contain" John AND Dealer 1 "does not contain" Jacob, and so on for 100 dealers)
Query 2: Source > Changed Type > Filtered Rows (This filters dealers out of the row that have received the lead already. To do this, I created a filter for the row as follows: Column: Dealer 2 "does not contain" John AND Dealer 2 "does not contain" Jacob, and so on for 100 dealers)
Query 3: Source > Changed Type > Filtered Rows (This filters dealers out of the row that have received the lead already. To do this, I created a filter for the row as follows: Column: Dealer 3 "does not contain" John AND Dealer 3 "does not contain" Jacob, and so on for 100 dealers)
5. Now I have 3 different sheets for each filter, and I do not know which is most accurate.
Remember, the list of 100 dealers that already have these leads. My goal is to filter these dealers out so I can inform those who have not received the leads.
