2 Lists with Different Name Structures

bobgrand

bobgrand

Active Member
Joined
Apr 14, 2008
Messages
252
Office Version
  1. 365
Platform
  1. Windows
Hello all,

Please bear with me as I will try to explain this as best I can,,,

I have 2 lists of customers. Our ERP system stores a 6 Character Customer Code and the full Customer Name we have created over many years. A list from our vendor who supports these customers with rebates is a Customer SPA# and Customer Name. The Customer Names between the 2 lists don't exactly match. I need to put our Customer Code in a column next to the Customer Name from the Vendor List. Most of the names did match so doing a V-lookup was very helpful. This part V-lookup didn't quite help. I was searching ways to return values by matching specific wording but not having any luck.

So here I am turning to the best of the best for some help.

Any guidance would be greatly appreciated.


Cust Match 032523.xlsx
ABCDEF
1LIST FROM OUR ERP SYSTEMLIST FROM VENDOR
2CUST-CODECustomer NameSPA#Customer NameCUST-CODE
3ADCCOMADC COMMUNICATIONS &2014404199ADC COMMUNICATIONS
4ALLOHOAL LO HOLDING CO. INC2018878200AL LO HOLDING COMPANY
5ALPEPEAL PEPE & SONS INC9086470170AL PEPE AND SONS
6ALIELEALI ELECTRICAL CONTRACTOR INC0000487935ALI ELECTRICAL CONTRACTOR
7ALLUTIALLAN BRITEWAY ELECTRICAL*2017850414ALLAN BRITEWAY ELECTRIC CO
8ALLWAYALL WAYS ELECTRIC LLC0000392195ALLWAYS ELECTRIC
9ALMELEALM ELECTRIC CO. INC.*9733169155ALM ELECTRIC
10AMEDRIAMEC ELECTRIC LLC*9087573040AMEC ELECTRIC
11AMERIEAMERICAN ELECTRICAL CONT0000537481AMERICAN ELECTRICAL CONTRACTOR
12FERCORAMES ADVANCED MATERIALS CORP0000537484AMES AMC
13AMNPHAAMNEAL PHARMACEUTICALS0000391169AMNEAL PHARMACEUTICALS LLC
14AMPCELAMPCORE ELECTRIC LLC0000147837AMPCORE ELEC
15APEXAPEX ELECTRICAL*9732384484APEX ELECTRIC INC
16APOMACAPOLLO MAC LLC8622212518APOLLO MAC, LLC
17ASHELEASHTIN ELECTRICAL CONTRACTORS7322615772ASHTIN ELECTRIC
18AUTROLAUTOMATIC ROLLS OF NJ INC.0000537878AUTOMATIC ROLLS
19ESMIBAYSHORE SOIL MANAGEMENT LLC0000537489BAY SHORE SOIL MANAGEMENT LLC
20BEAELEBEACH ELECTRIC CO*9736847421BEACH ELECTRIC CO. INC.
21BENGEOBENDER ELECTRIC INC.9082418980BENDER ENTERPRISES INC
22BINSNYBINSKY & SNYDER SERVICE INC7328850700BINSKY & SNYDER, INC
23BOZELEBOZ ELECTRICAL CONTRACTORS*9737642800BOZ ELECTRICAL CONTRACTOR
24BULELEBULLDOG ELECTRICAL INC.7322190433BULLDOG ELECTRIC OF LINCROFT
25WEAPROBURNETT ELECTRIC INC.*2013391909BURNETT ELECTRIC
26BWELECBW ELECTRIC SERVICES LLC*9082810660BW ELECTRICAL SERVICES LLC
27CAMPAVCAMBRIDGE PAVERS INC0000146748CAMBRIDGE PAVERS, INC.
Sheet1
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
hi there. It is helpful to know what version of excel you are using for this task. Can you update your user profile to have the excel version(s) you use? If you use more than one, please tell us in the comments here what version this task is using. Thank you.
 
Upvote 0
Hi,
Is there an underlying logic in the 6 character code construction ?
Could following formula help :
Excel Formula: 
=LEFT(SUBSTITUTE(E3," ",""),6)
 
Upvote 0
awoohaw said:
hi there. It is helpful to know what version of excel you are using for this task. Can you update your user profile to have the excel version(s) you use? If you use more than one, please tell us in the comments here what version this task is using. Thank you.
Click to expand...
The office version I use daily is Office 365. The version I used XL2BB with is from office 2013.
 
Upvote 0
Okay, try this. It matches first 10 letters. Not sure if it will work. You need to input it using CNTRL-SHFT-ENTER

mr excel questions 17.xlsm
ABCDEF
1LIST FROM OUR ERP SYSTEMLIST FROM VENDOR
2CUST-CODECustomer NameSPA#Customer NameCUST-CODE
3ADCCOMADC COMMUNICATIONS &2014404199ADC COMMUNICATIONSADCCOM
4ALLOHOAL LO HOLDING CO. INC2018878200AL LO HOLDING COMPANYALLOHO
5ALPEPEAL PEPE & SONS INC9086470170AL PEPE AND SONSALPEPE
6ALIELEALI ELECTRICAL CONTRACTOR INC0000487935ALI ELECTRICAL CONTRACTORALIELE
7ALLUTIALLAN BRITEWAY ELECTRICAL*2017850414ALLAN BRITEWAY ELECTRIC COALLUTI
8ALLWAYALL WAYS ELECTRIC LLC0000392195ALLWAYS ELECTRICALLWAY
9ALMELEALM ELECTRIC CO. INC.*9733169155ALM ELECTRICALMELE
10AMEDRIAMEC ELECTRIC LLC*9087573040AMEC ELECTRICAMEDRI
11AMERIEAMERICAN ELECTRICAL CONT0000537481AMERICAN ELECTRICAL CONTRACTORAMERIE
12FERCORAMES ADVANCED MATERIALS CORP0000537484AMES AMCFERCOR
13AMNPHAAMNEAL PHARMACEUTICALS0000391169AMNEAL PHARMACEUTICALS LLCAMNPHA
14AMPCELAMPCORE ELECTRIC LLC0000147837AMPCORE ELECAMPCEL
15APEXAPEX ELECTRICAL*9732384484APEX ELECTRIC INCAPEX
16APOMACAPOLLO MAC LLC8622212518APOLLO MAC, LLCAPOMAC
17ASHELEASHTIN ELECTRICAL CONTRACTORS7322615772ASHTIN ELECTRICASHELE
18AUTROLAUTOMATIC ROLLS OF NJ INC.0000537878AUTOMATIC ROLLSAUTROL
19ESMIBAYSHORE SOIL MANAGEMENT LLC0000537489BAY SHORE SOIL MANAGEMENT LLCESMI
20BEAELEBEACH ELECTRIC CO*9736847421BEACH ELECTRIC CO. INC.BEAELE
21BENGEOBENDER ELECTRIC INC.9082418980BENDER ENTERPRISES INCBENGEO
22BINSNYBINSKY & SNYDER SERVICE INC7328850700BINSKY & SNYDER, INCBINSNY
23BOZELEBOZ ELECTRICAL CONTRACTORS*9737642800BOZ ELECTRICAL CONTRACTORBOZELE
24BULELEBULLDOG ELECTRICAL INC.7322190433BULLDOG ELECTRIC OF LINCROFTBULELE
25WEAPROBURNETT ELECTRIC INC.*2013391909BURNETT ELECTRICWEAPRO
26BWELECBW ELECTRIC SERVICES LLC*9082810660BW ELECTRICAL SERVICES LLCBWELEC
27CAMPAVCAMBRIDGE PAVERS INC0000146748CAMBRIDGE PAVERS, INC.CAMPAV
bobgrand
Cell Formulas
RangeFormula
F3:F27F3= INDEX($A$3:$A$27,(--(ISNUMBER(FIND(LEFT($B$3:$B$27,MIN(10,LEN($B$3:$B$27))),$B$3:$B$27))))*(ROW($B$3:$B$27)-2))
Dynamic array formulas.
 
Upvote 0
bobgrand said:
The office version I use daily is Office 365. The version I used XL2BB with is from office 2013.
Click to expand...
the solution i offer above will work with 365, if you are using it in 2013, it uses matrix multiplication and array formulas. The array formula must be entered in 2013 with the CNTL-SHFT-ENTER.
 
Upvote 0
James006 said:
Hi,
Is there an underlying logic in the 6 character code construction ?
Could following formula help :
Excel Formula: 
=LEFT(SUBSTITUTE(E3," ",""),6)
Click to expand...
James006 said:
Hi,
Is there an underlying logic in the 6 character code construction ?
Could following formula help :
Excel Formula: 
=LEFT(SUBSTITUTE(E3," ",""),6)
Click to expand...
You bring up a good point here. The list I posted are how the names look differently from one to the other. Our ERP system list is much larger than the vendor list so the names wouldn't line up along side each other exactly or else I would have just copy and pasted the customer codes.

My fault for not explaining that.

For example the first customer in the list is in row 25 of our system list but its row 8 in the vendor list. We have 480 customers in our system. The vendor list is only 123 customers. So nothing would line up exactly side by side.
 
Upvote 0
I used a fuzzy join in Power Query. Load each table to the PQ editor and then join them with a .6 criteria on the common name

Power Query: 
let
    Source = Table.FuzzyNestedJoin(Table2, {"Customer Name"}, Table1, {"Customer Name"}, "Table1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=.6]),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"CUST-CODE"}, {"CUST-CODE"})
in
    #"Expanded Table1"

Book1
HIJ
2SPA#Customer NameCUST-CODE
32014404199ADC COMMUNICATIONSADCCOM
42018878200AL LO HOLDING COMPANYALLOHO
59086470170AL PEPE AND SONSALPEPE
6487935ALI ELECTRICAL CONTRACTORALIELE
72017850414ALLAN BRITEWAY ELECTRIC COALLUTI
8392195ALLWAYS ELECTRICALLWAY
99733169155ALM ELECTRICALMELE
109733169155ALM ELECTRICALLWAY
119733169155ALM ELECTRICALIELE
129087573040AMEC ELECTRICAMEDRI
13537481AMERICAN ELECTRICAL CONTRACTOR
14537484AMES AMC
15391169AMNEAL PHARMACEUTICALS LLCAMNPHA
16147837AMPCORE ELEC
179732384484APEX ELECTRIC INCAPEX
188622212518APOLLO MAC, LLCAPOMAC
197322615772ASHTIN ELECTRICASHELE
20537878AUTOMATIC ROLLSAUTROL
21537489BAY SHORE SOIL MANAGEMENT LLCESMI
229736847421BEACH ELECTRIC CO. INC.BEAELE
239082418980BENDER ENTERPRISES INC
247328850700BINSKY & SNYDER, INCBINSNY
259737642800BOZ ELECTRICAL CONTRACTORBOZELE
267322190433BULLDOG ELECTRIC OF LINCROFT
272013391909BURNETT ELECTRICWEAPRO
289082810660BW ELECTRICAL SERVICES LLCBWELEC
29146748CAMBRIDGE PAVERS, INC.CAMPAV
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
need macro that can create multiple tabs and transfer data from 2 tabs that already have data
Replies
0
Views
651
Tonyk1051
T
H
  • Solved
VBA to consolidate list
Replies
7
Views
572
hwong8848
H
G
  • Question Question
Fuzzy Match code
Replies
1
Views
2K
gelu
G
C
  • Solved
VBA Check if value in Sheet1 exists in Sheets(2-40), then copy found values from sheet 1 to sheet(2-40)
2
Replies
13
Views
2K
CobusVanWyk
C
T
  • Question Question
Evaluate beginning of cell, compare to list, and return another cell's contents.
Replies
2
Views
689
TLKlysen
T

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top