Hi there
I have a list of 20K potential leads. I need to analyse which leads are already customer. The only parameter upon which I can do the matching search is the name of the company. The company's name is mentioned in both xls databases but not always in exactly the same way.
What is the best way to match both databases as to identify which leads are already customer.
Best regards
DOJA
Look at doing a fuzzy match in Power Query called Get and Transform in your version and found on the Data Tab. If you post a sample of both tables, I can demo later today.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi
I added a file. There are 2 identifiers. In List 1 account & zip code and in List 2 Account name & zip code. In my business it happens a lot that multiple accounts have similar names so the notion of postal code is very important.
Thnx in advance!
Doja
Thnx Alan for your answer. I added a sample file. Would you be so kind as to have a look at it?
I have tried to use a fuzzy match and the disparities are too great to return any results save the exact matches. Hopefully, you will find another means to solving this.
Thank you Alansidman!
Perhaps this will help.
Although it isn't exact, it may give an indication of those that are and are not current customers.
Four columns compare ACCOUNT NAME, Building Address, City and Zip Code between list1 and list2.
The Name Match column uses: =ISNUMBER(MATCH(TRIM(A2),LIST1!B$3:B$5,0))
The Building Address Match column uses: =ISNUMBER(MATCH("*"&B2&"*",LIST1!C$3:C$5,0))
The City Match column uses: =ISNUMBER(MATCH(TRIM(RIGHT(C2,LEN(TRIM(C2))-IF(ISNUMBER(SEARCH(" ",TRIM(C2))),SEARCH(" ",TRIM(C2)),0))),LIST1!F$3:F$5,0))
The Zip Code Match column uses: =ISNUMBER(MATCH(D2,LIST1!E$3:E$5,0))
A fifth column displays a percentage of matches using: =COUNTIFS(F2:I2,TRUE)/4
You could then filter based on the % Current Customer column, using the smallest amount, to get businesses that are least likely to be current customers.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
JeteMc, Thanks a lot for your answer. I'll need some time to understand your formulaes but the result already helps me a lot. THNX! Doja
You're Welcome and thank you for the feedback. I hope that you have a blessed day.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks