Working on project that requires me to filter rows blank rows

Copper Contributor

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!

11 Replies

@jjsoto 

Perhaps Power Query will do the job, but it's better to discuss on concrete sample.

@Sergei Baklan 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

@Sergei Baklan 

 

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.

@jjsoto 

If I understood correctly you'd like to extract names of dealers who doesn't have leads, like this

image.png

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.

 

I am not understanding your directions. I split the columns using "," as a delimiter, that is how I accomplished Step 2 from my previous post.

My goal is to filter out all leads with dealers that have not been notified. That is why I have the filters set up as "does not contain".

@jjsoto 

Let forget about formulas for a while. Source and result are as in my sample, or it shall be something different? If different, could you please provide the sample, as simple as my one, just to illustrate the task.

@Sergei Baklan 

 

I think I understand what I need to do now. I split the columns and then unpivoted the 3 columns, as per your suggestion. Now, I should be able to apply a filter for the 100 dealers across the Value column and only be left with the dealers that have not received the leads.

 

Is this correct? If so, I have the list of dealers in another spreadsheet. Can I use that to filter them out without having to type them all in individually using the Filter function in Power Query?

@jjsoto 

If you already have the list of names, of course you may query and use it. I'd only recommend Trim, Clean and perhaps lowercase (or uppercase) names in both queries (all in Transform->Format) to avoid as much as possible misspelling errors in names. On final step you may apply Capitalize Each Word to names.

 

IMHO, simple filter won't work or requires a lot of manual work, I'd use third query as described in my previous post. 

@Sergei Baklan 

 

Thank you, but you may be over estimating my knowledge of Excel. How could I use the list of 100 leads to create a filter for the Value column? 

@jjsoto 

My understanding you need to filter one table on values which do exist in another table, but not in first one. That's the opposite to "filter by name" task.

 

Let try another way, perhaps it'll be more suitable for you. 

image.png

Let assume we have table1 with leads and names, table2 with all names and "green" is what we'd like to receive.

 

Query table 1 and table2. To start with green table in power query right click on first query and Reference. Here Add Custom Column as

image.png

Expand AllNames column and add another Custom column as

image.png

Group By on these two columns with Sum aggregation of Custom column

image.png

Filter Count column keeping zeroes and after that remove this column

image.png

Group By first column using as aggregation Sum of Name.1

image.png

We will receive errors, for proper result we shall replace in formula bar this part

image.png

on

image.png

 

You may check steps in attached file.