Populate cell based on similar text

Question

Hello,

I have a list of customer accounts (see column to the left). These account names are all slightly different names. For example, some have an LLC; others may have the suite name, etc. In another column, I would like to group similar account names together. The output of the grouping doesn't matter to me. It can be common text found under the account name (see example output 1) or a number (see example out 2).

Account Names	Example output 1	example output 2
John allergy center of America	John allergy center	1
John allergy center	John allergy center	1
Logen medical center	Logen medical center	2
Specialty clinic	Specialty clinic	3
Specialty clinic LLC	Specialty clinic	3
John allergy center of America - Suite 4	John allergy center	1

Answer 1

Unfortunately, I don’t.

Answer 2

Hi,

Do you already have a proper list of names with you somewhere that can be used. If you have that, then a fuzzy lookup can be performed.

Answer 3

https://club.excelhome.net/forum.php?mod=viewthread&tid=1665148&fromguid=hot&extra=&mobile&\_dsign=735efe0a

fuzz match like above？

Answer 4

@Shakiru

I ran the code, and it did not work. The code created two additional columns. Column B = Column A and Column C:C = Column A1

Column A	Column B	Column C
john allergy center of America	john allergy center of America	john allergy center of America
John allergy center	John allergy center	john allergy center of America
Logen medical center	Logen medical center	john allergy center of America
Specialty clinic	Specialty clinic	john allergy center of America
Specialty clinic LLC	Specialty clinic LLC	john allergy center of America
John allergy center of America - Suite 4	John allergy center of America - Suite 4	john allergy center of America

Answer 5

Hi Oscar R!

If you have many inputs data and want that to run automatically, I suggest you tried using the VBA code.

Please try this: Sub GroupAccountNames() Dim accountNamesRange As Range Dim accountNameCell As Range Dim groupedNamesRange As Range Dim groupedNamesCell As Range Dim cleanedName As String Dim uniqueNames As Collection Dim groupName As String Dim groupNumber As Integer

' Set the range of account names Set accountNamesRange = Range("A2:A" & Cells(Rows.Count, 1). End(xlUp). Row)

' Set the range for grouped names (output) Set groupedNamesRange = Range("B2:C" & accountNamesRange.Rows.Count + 1)

' Initialize the collection for unique names Set uniqueNames = New Collection

' Loop through each account name For Each accountNameCell In accountNamesRange ' Remove common variations like LLC, Suite, etc. cleanedName = LCase(Replace(accountNameCell.Value, "llc", "", , , vbTextCompare)) cleanedName = LCase(Replace(cleanedName, "suite", "", , , vbTextCompare)) cleanedName = Trim(cleanedName)

' Check if the cleaned name is already in the unique names collection On Error Resume Next groupName = uniqueNames.Item(cleanedName) On Error GoTo 0

If groupName = "" Then ' Add the cleaned name to the unique names collection and create a new group uniqueNames.Add cleanedName, cleanedName groupNumber = uniqueNames.Count groupName = cleanedName End If

' Write the original account name, common name, and group number to the grouped names range Set groupedNamesCell = groupedNamesRange.Cells(accountNameCell.Row - 1, 1) groupedNamesCell.Value = accountNameCell.Value groupedNamesCell.Offset(0, 1). Value = groupName groupedNamesCell.Offset(0, 2). Value = groupNumber Next accountNameCell End Sub

Here's how to use the code: * Open your Excel worksheet containing the account names. * Press Alt + F11 to open the Visual Basic Editor. * Insert a new module by clicking Insert -> Module. * Copy and paste the above VBA code into the new module. Close the Visual Basic Editor. * In your Excel worksheet, click on a cell where you want to start the grouped names output. * Go to the Developer tab (if it's not visible, enable it in Excel settings) and click on Macros. * In the Macros dialog box, select the GroupAccountNames macro and click Run.

Please note that this VBA code assumes that the account names are in column A, starting from the second row. Adjust the accountNamesRange and groupedNamesRange variables in the code if your data is located elsewhere.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru