## Harmonizing Company Lists with Excel: An Advanced Matching Approach

##### Description

This project involves using Excel to compare two lists of company names and identify those appearing in one list but not the other. The unique challenge lies in accounting for variations in spelling or formatting, which necessitates advanced techniques and formulas. We'll use Excel's capabilities, such as fuzzy matching, to ensure an accurate comparison. Also, practical demonstrations and exercises will cement these techniques.

The original prompt:

I have two spreadsheets that have two columns of company names and Excel. Many of the companies are similar, but they have slightly different spellings but is at the same company. How can I find which companies are on list A but not on list B despite the fact that the companies that are on both lists may have slightly different spellings. Can you give me an ex lookup formula that will find the companies with similar spellings that are actually the same company?

# Understanding the Basics of List Comparison in Excel

## Introduction

List comparison in Excel is a fundamental skill, especially useful when trying to identify and reconcile data across multiple spreadsheets. This guide focuses on comparing company names in two lists, even with slight spelling variations.

## Steps to Compare Lists in Excel

### 1. Preparing Your Data

Ensure your data is organized in two columns:

- Column A: List of company names from Spreadsheet 1
- Column B: List of company names from Spreadsheet 2

### 2. Exact Match Comparison

To find exact matches between the lists, use the `VLOOKUP`

or `MATCH`

function.

#### Using VLOOKUP

- In Column C, use the following formula:
`=VLOOKUP(A2, $B$2:$B$100, 1, FALSE)`

- Drag the formula down through all rows in Column A.
- Any cell that returns an error indicates that the company name in Column A does not have an exact match in Column B.

#### Using MATCH

- In Column C, use the following formula:
`=IF(ISNUMBER(MATCH(A2, $B$2:$B$100, 0)), "Match", "No Match")`

- Drag the formula down through all rows in Column A.
- Cells showing "Match" indicate a corresponding name found in Column B.

### 3. Fuzzy Matching for Minor Spelling Differences

Excel does not have built-in fuzzy matching, but we can use helper columns and logical formulas.

#### Creating a Helper Column for Standard Comparison

- Normalize company names by removing spaces and converting to uppercase.
- In Column D for Spreadsheet 1 and Column E for Spreadsheet 2, use the following formula:
`=UPPER(TRIM(SUBSTITUTE(A2, " ", "")))`

#### Comparing Normalized Names

- Use the following formula in Column F to compare the normalized names:
`=IF(ISNUMBER(MATCH(D2, $E$2:$E$100, 0)), "Possible Match", "No Match")`

### 4. Advanced Fuzzy Matching using Add-Ins or Power Query

If more advanced fuzzy matching is necessary, Excel add-ins like Fuzzy Lookup Add-In for Excel or Power Query can be used. Below is a straightforward approach using Power Query.

#### Using Power Query

- Load both tables (Spreadsheet 1 and 2) into Power Query.
- Merge queries with
`Fuzzy Matching`

option enabled.

Detailed steps:

- Go to
`Data`

>`Get & Transform Data`

>`Get Data`

>`From Table/Range`

. - Load the first table.
- Repeat the process for the second table.
- In Power Query Editor, use
`Home`

>`Merge Queries`

. - Select the corresponding columns to merge.
- Check the
`Use fuzzy matching`

box and configure the settings as needed.

### 5. Final Reconciliation

After obtaining lists of exact and possible matches, manually review for final reconciliation. This typically involves:

- Reviewing non-matched entries.
- Correcting significant discrepancies manually.

## Conclusion

By following these steps, you can effectively compare and reconcile company names across two spreadsheets in Excel, even with minor spelling differences. This method ensures a clear process from exact match identification to advanced fuzzy matching for more complex scenarios.

## Introduction to Fuzzy Matching Techniques

### Part 2: Practical Plan to Identify and Reconcile Company Names in Excel

**Objective:** Reconcile company names across two spreadsheets with slight variations in spelling using Excel's capabilities.

### Step-by-Step Implementation

#### 1. Using the `Fuzzy Lookup`

Add-In

Fuzzy Lookup is an Excel add-in provided by Microsoft. It is specifically designed to perform fuzzy matching between tables.

**Installation:**

- Download the Fuzzy Lookup Add-in from the official Microsoft website.
- Run the installer and follow the instructions.

**Application:**

- Open the spreadsheets you want to reconcile.
- Go to the
`Fuzzy Lookup`

tab in Excel.

**Creating Fuzzy Matches:**

**Define the Tables:**- Select the range in your first spreadsheet and convert it to a table (Ctrl+T).
- Name the table (e.g.,
`Table1`

) by going to`Table Tools`

->`Design`

->`Table Name`

.

**Repeat for the Second Table:**- Select the range in the second spreadsheet and convert it to a table.
- Name it (e.g.,
`Table2`

).

**Perform Fuzzy Lookup:**- In Excel, navigate to the
`Fuzzy Lookup`

tab. - Drag
`Table1`

to the`Left Table`

section and`Table2`

to the`Right Table`

section. - Choose the fields you want to match (e.g.,
`CompanyName`

from both tables). - Set a similarity threshold (e.g., 0.85) under
`Fuzzy Lookup`

. - Click
`Go`

.

- In Excel, navigate to the
**Review Results:**- The results will show a new table with matches from both spreadsheets and a similarity score.
- You can adjust the similarity score if you find too many or too few matches.

#### 2. Using Excel Formulas for Basic Fuzzy Matching

If you do not have access to the Fuzzy Lookup add-in, you can use Excel's built-in functions in combination with some formulas to perform basic fuzzy matching.

**1. Create a New Column for Matches:**

In the first table:

`Formula: =INDEX(Table2[CompanyName], MATCH(TRUE, ISNUMBER(SEARCH(Table1[@CompanyName], Table2[CompanyName])), 0))`

`Explain: This formula searches for partial matches of `CompanyName` from `Table1` in `Table2` and returns the first match.`

**2. Use LEVENSHTEIN Distance Function:**

For more precise matching, use a user-defined function (UDF) to calculate Levenshtein distance.

Open the Excel VBA editor (Alt + F11).

Insert a new module and paste the following UDF:

```
Function Levenshtein(s1 As String, s2 As String) As Long
Dim i As Long, j As Long, cost As Long
Dim d() As Long
ReDim d(0 To Len(s1), 0 To Len(s2))
For i = 0 To Len(s1)
d(i, 0) = i
Next i
For j = 0 To Len(s2)
d(0, j) = j
Next j
For i = 1 To Len(s1)
For j = 1 To Len(s2)
If Mid(s1, i, 1) = Mid(s2, j, 1) Then cost = 0 Else cost = 1
d(i, j) = Application.WorksheetFunction.Min(d(i - 1, j) + 1, _
d(i, j - 1) + 1, _
d(i - 1, j - 1) + cost)
Next j
Next i
Levenshtein = d(Len(s1), Len(s2))
End Function
```

**3. Apply the UDF in the Worksheet:**

To use this UDF to compare company names:

`Formula: =Levenshtein(A2, B2)`

Where `A2`

and `B2`

are cells containing company names from `Table1`

and `Table2`

respectively.

**4. Use Conditional Formatting:**

For visual representation, apply conditional formatting based on the Levenshtein distance:

- Select the range where you have applied the UDF.
- Go to
`Home`

->`Conditional Formatting`

->`New Rule`

. - Use a formula to determine which cells to format:
- For example:
`=C2 < 5`

to highlight cells with a Levenshtein distance less than 5.

- For example:
- Choose a formatting style (e.g., fill color).

### Conclusion

This guide provides a practical solution to reconciling company names in Excel by utilizing both the Fuzzy Lookup add-in and custom formulas/UDFs for basic fuzzy matching. Apply these methods to efficiently resolve inconsistencies in company names across different spreadsheets.

## Advanced Lookup Formulas for Reconciliation of Company Names in Excel

To reconcile company names across two Excel spreadsheets with slightly different spellings, we will use a combination of various advanced functions such as `INDEX`

, `MATCH`

, and `Fuzzy Lookup Add-in`

. Here’s how to effectively reconcile such differences:

### Step-by-Step Instructions:

**Combine Data into One Sheet for Analysis:**- Copy the two company name lists into a single Excel sheet in two adjacent columns for easy referencing.
`Sheet1`

from`FileA.xlsx`

to`Column A`

`Sheet1`

from`FileB.xlsx`

to`Column B`

- Copy the two company name lists into a single Excel sheet in two adjacent columns for easy referencing.
**Normalize Data with Helper Columns:**- Add helper columns to normalize the data for comparison.

`=TRIM(LOWER(A2)) // Normalizes the data in `Column A` =TRIM(LOWER(B2)) // Normalizes the data in `Column B``

**Create a Lookup Table Using Fuzzy Matching:**If the Fuzzy Lookup Add-in is available, we can use it to create a lookup table of likely matches between the two lists. After installing and enabling the add-in:**Create the Fuzzy Lookup Table:**- Select the range for both normalized helper columns.
- Use Fuzzy Lookup to produce a list of matches along with similarity scores.

Assuming output columns for matches are:

- Column C ->
`Column A Matches`

- Column D ->
`Column B Matches`

- Column E ->
`Similarity Score`

### Implementing the Formulas

**Identify Closest Matches:**- Use the Fuzzy Lookup results to identify and report the closest matches.

`=IF(E2 >= 0.85, D2, "No close match") // Adjust similarity threshold as necessary`

- In this formula,
`E2`

is the similarity score, and we check if it is above the threshold (0.85 in this case). If it is, the corresponding name from`Column D`

(matched from`Column B`

) is selected.

**Use INDEX and MATCH for Exact Lookup:**- For exact matches from the normalized helper columns:

`=INDEX(B:B, MATCH(A2, B:B, 0)) // Exact match for data in Column A against Column B`

**Combining Results:**Create a final column to display combined results:

`=IF(ISNUMBER(MATCH(A2, B:B, 0)), INDEX(B:B, MATCH(A2, B:B, 0)), IF(E2 >= 0.85, D2, "No match found"))`

- This formula checks if an exact match exists; if not, it checks the similarity score and provides the closest fuzzy match if available.

### Conclusion

By effectively combining Excel’s `INDEX`

, `MATCH`

, and the Fuzzy Lookup Add-in, you can efficiently reconcile company names across two spreadsheets, even when they have slightly different spellings.

This implementation provides a straightforward approach to handle various spelling discrepancies and ensure accurate matching in your Excel sheets.

## Practical Implementation and Troubleshooting

### Objective

To identify and reconcile company names across two spreadsheets in Excel, even when they have slightly different spellings using fuzzy matching techniques and advanced lookup formulas.

### Implementation

#### Step 1: Prepare Data

Assume `Sheet1`

and `Sheet2`

contain the company names to be reconciled.

#### Step 2: Enable Fuzzy Matching

- Go to
`Data`

tab >`Get & Transform Data`

group >`Get Data`

>`Combine Queries`

>`Merge`

. - Select
`Sheet1`

and`Sheet2`

to merge. - Check
`Use fuzzy matching to perform the merge`

.

#### Step 3: Merge with Fuzzy Matching

- Choose
`Company Name`

column from`Sheet1`

and`Sheet2`

. - Set
`Fuzzy Matching Options`

:**Similarity Threshold**:`0.8`

(Adjust based on the desired accuracy).**Transformation Table**: Optional (If you have a pre-defined table of common misspellings or abbreviations).

#### Step 4: Ensure Unique Identifiers

Ensure both sheets have unique identifiers for each company.

#### Step 5: Create a Helper Column

- Create a
`Helper Column`

in both sheets which concatenates key identifying information (e.g., address, zip code).

#### Example Formula

`=CONCATENATE(A2, B2, C2)`

Where `A`

, `B`

, and `C`

are columns with identifying information.

#### Step 6: Perform Advanced Lookup

Use the `XLOOKUP`

function to search for fuzzy matches.

###### In `Sheet1`

:

`=XLOOKUP([@CompanyName], Sheet2[HelperColumn], Sheet2[CompanyName], "Not Found", 2, 1)`

- The
`2`

in the formula specifies the`match_mode`

for fuzzy matching. - The
`1`

specifies`search_mode`

.

#### Step 7: Automate Reconciliation

Automate the reconciliation process using VBA.

##### VBA Code Sample:

```
Sub ReconcileCompanyNames()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws1LastRow As Long, ws2LastRow As Long
Dim i As Long, j As Long
Dim similarityScore As Double
Dim threshold As Double: threshold = 0.8
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
ws1LastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
ws2LastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
For i = 2 To ws1LastRow
For j = 2 To ws2LastRow
similarityScore = Application.WorksheetFunction.FuzzyLookup(ws1.Cells(i, 1).Value, ws2.Cells(j, 1).Value)
If similarityScore >= threshold Then
ws1.Cells(i, 2).Value = ws2.Cells(j, 1).Value
Exit For
End If
Next j
Next i
End Sub
```

#### Troubleshooting

**Mismatch Results**: Adjust the similarity threshold.**Performance Issues**: Optimize by limiting the range of lookup.**False Positives**: Enhance helper columns with more unique identifiers.

This practical implementation ensures that you can reconcile company names across two spreadsheets using Excel’s in-built fuzzy matching and VBA for automation. Apply these steps directly in Excel to manage variations in spellings effectively.

# Case Studies and Real-World Applications: Identifying and Reconciling Company Names in Excel

## Problem Overview

You need to identify and reconcile company names across two spreadsheets in Excel, even when they have slight variations in spelling. This involves leveraging Excel's powerful functions and fuzzy matching capabilities to achieve accurate matches.

## Steps to Implement

### 1. Load Data into Excel

- Open Excel.
- Load the two spreadsheets that contain the company names you need to reconcile. Assume Spreadsheet A has company names in column
`A`

and Spreadsheet B has company names in column`B`

.

### 2. Using Excel Functions for Fuzzy Matching

Excel does not have built-in fuzzy matching functions, so we'll use a combination of nested functions and helper columns to achieve our goal.

**Create Helper Columns:**- Insert two new columns in Spreadsheet A (let's call them
`Helper1`

and`Helper2`

). - In cell
`Helper1`

(A2), use the basic normalization formula to convert all characters to lower case and remove extra spaces:`=LOWER(TRIM(A2))`

- Drag the formula down to apply it to all rows in column
`A`

. - Repeat the same process in Spreadsheet B using columns
`Helper3`

and`Helper4`

.

- Insert two new columns in Spreadsheet A (let's call them
**Levenshtein Distance Calculation:**Excel does not have a built-in function for Levenshtein distance, so we create a User Defined Function (UDF).

Press

`Alt+F11`

to open the VBA editor.Insert a new module and paste the following VBA code:

`Function Levenshtein(s1 As String, s2 As String) As Long Dim i As Long, j As Long Dim m As Long, n As Long Dim d() As Long Dim cost As Long m = Len(s1) n = Len(s2) ReDim d(0 To m, 0 To n) For i = 0 To m d(i, 0) = i Next i For j = 0 To n d(0, j) = j Next j For i = 1 To m For j = 1 To n If Mid(s1, i, 1) = Mid(s2, j, 1) Then cost = 0 Else cost = 1 d(i, j) = Application.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + cost) Next j Next i Levenshtein = d(m, n) End Function`

**Apply Levenshtein Distance:**- Close the VBA editor and return to Excel.
- Create new helper columns in both spreadsheets for storing Levenshtein distances.
- Use the Levenshtein function to calculate the distance for each pair:
`=Levenshtein(Helper1!A2, Helper3!B2)`

- Copy the formula down for all rows to compute distances.

### 3. Matching and Reconciling

**Set a Threshold for Matching:**- Define a threshold for the Levenshtein distance that you consider acceptable for a match (e.g., 3).
- Create another column where you check if the distance is within the threshold:
`=IF(Levenshtein(Helper1!A2, Helper3!B2) <= 3, "Match", "No Match")`

- Copy the formula down for all rows.

**Review and Verify:**- Manually review and verify matches flagged as "Match" to ensure they are accurate.
- Correct any mismatches or false positives as required.

## Conclusion

By implementing the above steps and using Excel's capabilities along with VBA, you can effectively identify and reconcile company names across two spreadsheets, even with slight variations in spelling. This approach has practical applications in various data reconciliation tasks in real-world business scenarios.

# Identifying and Reconciling Company Names Using XLOOKUP in Excel

To identify and reconcile company names across two spreadsheets in Excel even when they have slightly different spellings, you can leverage the `XLOOKUP`

function. `XLOOKUP`

offers more flexibility and robustness compared to `VLOOKUP`

and allows for better matching capabilities including exact matches, wildcard matches, and approximate matches.

Here's how you can implement it:

### Steps to Implement XLOOKUP for Company Name Reconciliation

**Open Your Spreadsheet:**Ensure you have your two spreadsheets open in Excel. One will be considered the`source`

file (e.g., "Sheet1") and the other the`target`

file (e.g., "Sheet2").**Formula Setup:**In your`target`

file (let's assume this is "Sheet2"), you want to use`XLOOKUP`

to match names from the`source`

file (Sheet1).**Target File (Sheet2):**- Column A: Company Names (with slight spelling differences to match with the source).
- Column B (empty): This is where you'll place your reconciliation results.

**Source File (Sheet1):**- Column A: Original Company Names.
- Column B: Additional data you might want to pull, such as Standardized Company Names, Addresses, etc.

**Using XLOOKUP with Wildcards:**Place the following`XLOOKUP`

formula in Cell B2 of the`target`

file (Sheet2):`=XLOOKUP("*" & A2 & "*", Sheet1!A:A, Sheet1!B:B, "Not Found", 2)`

`*`

& A2 &`*`

: This encompasses a wildcard search which attempts to match any string containing the text in A2.`Sheet1!A:A`

: The range in the source file where the lookup should be performed.`Sheet1!B:B`

: The range in the source file where the corresponding value should be returned from.`"Not Found"`

: The default text if no match is found.`2`

indicates a wildcard match search mode.

**Apply the Formula:**Drag the formula in Cell B2 down across all rows in Column B of Sheet2 to apply it to each company name in your target file.

### Example

If you have the following data:

**Sheet1: Source File**

A | B |
---|---|

Alpha Inc. | Alpha Inc. |

Beta Ltd. | Beta Ltd. |

Gamma Co. | Gamma Co. |

**Sheet2: Target File**

A | B |
---|---|

Alpha | |

Beta Limited | |

Gamma Company |

After applying the formula, Sheet2 would be filled as:

**Sheet2: Target File**

A | B |
---|---|

Alpha | Alpha Inc. |

Beta Limited | Not Found |

Gamma Company | Not Found |

You can further refine using combinations of `TEXTJOIN`

, `MATCH`

, and other Excel features, but this basic `XLOOKUP`

with wildcards should help get started.

### Conclusion

Implementing the above `XLOOKUP`

formula will effectively reconcile company names by matching even with slight discrepancies in their spellings. This method presumes that the differences are minor and fall within pattern recognition abilities of Excel's wildcard functionality. For more complex variations and spelling errors, additional tools or techniques might be necessary.