08-07-2020 09:30 AM
08-07-2020 09:30 AM
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!
08-07-2020 09:44 AM
Perhaps Power Query will do the job, but it's better to discuss on concrete sample.
08-07-2020 01:06 PM - edited 08-07-2020 01:31 PM
@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?
08-07-2020 02:02 PM
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
08-10-2020 08:19 AM - edited 08-10-2020 08:19 AM
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.
08-10-2020 09:37 AM
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.
08-10-2020 09:46 AM
08-10-2020 09:55 AM
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.
08-10-2020 10:07 AM
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?
08-10-2020 10:21 AM
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.
08-10-2020 11:17 AM
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?
08-10-2020 02:27 PM
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.
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
Expand AllNames column and add another Custom column as
Group By on these two columns with Sum aggregation of Custom column
Filter Count column keeping zeroes and after that remove this column
Group By first column using as aggregation Sum of Name.1
We will receive errors, for proper result we shall replace in formula bar this part
You may check steps in attached file.