Hey experts, I am working on this project that requires me to import data from a CRM. Each row represents a contact, and if the row has a field (Column V, in this circumstance) blank, then I copy/paste it into a new sheet. Alternatively, I can delete all entries that have this field populated-- leaving me only with entries that have this field blank. I tried writing a macro for this, but got in way over my head. Is there any guidance on how to do this? Thanks in advance!

jjsoto 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?

jjsoto 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

SergeiBaklan 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/Range4. 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.

jjsoto If I understood correctly you'd like to extract names of dealers who doesn't have leads, like this If so you may query left (source) table, select Lead column, unpivot other columns and keep only Lead and Value. Refer to this query, keep only names value, sort and remove duplicates - you have here list of all names Refer to first query, group by leads, and in formula bar change each _, table... on each _ [Value] to keep only names for each lead. Add custom column with formula =Text.Combine(List.Difference(Names[Value],[Names]),", "), here we have difference of all names with names who have this lead. Keep only Lead and this column. If necessary you may split above custom column on columns using ", " as delimiter. You may check all steps in attached file.

Working on project that requires me to filter rows blank rows

11 Replies

SergeiBaklan
Diamond Contributor
Aug 07, 2020
jjsoto

Perhaps Power Query will do the job, but it's better to discuss on concrete sample.
- jjsoto
  Copper Contributor
  Aug 07, 2020
  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?
  - SergeiBaklan
    Diamond Contributor
    Aug 07, 2020
    jjsoto
    
    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

Forum Discussion

Working on project that requires me to filter rows blank rows

11 Replies

Resources