Using LibreOfficeCalc a spreadsheet alternative Married name and maiden name

edscollects

New Member
Joined
Feb 8, 2023
Messages
44
Office Version
  1. 2021
Platform
  1. Windows
I had this same question in excel and was never able to get a full answer. I want to compare 2 cells, the first has the name Joyce Thompson and the name Joyce Miller. This is the same person the first name is their maiden name and the second name is their married name. If someone could provide instruction in any spreadsheet program I would greatly appreciate it.
 
See if this provides what you are seeking :

VBA Code:
Sub CompareNames()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim nameA As Variant
    Dim nameB As Variant
    Dim word As Variant
    Dim matchFound As Boolean
    
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
    
    ' Find the last row with data in Column A or Column B
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If ws.Cells(ws.Rows.Count, "B").End(xlUp).Row > lastRow Then
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    End If
    
    ' Loop through each row
    For i = 1 To lastRow
        nameA = Split(ws.Cells(i, 1).Value, " ")
        nameB = Split(ws.Cells(i, 2).Value, " ")
        
        ' Initialize matchFound as False
        matchFound = False
        
        ' Check if any word in nameA exists in nameB
        For Each word In nameA
            If IsNumeric(Application.Match(word, nameB, 0)) Then
                matchFound = True
                Exit For
            End If
        Next word
        
        ' Check if any word in nameB exists in nameA
        If Not matchFound Then
            For Each word In nameB
                If IsNumeric(Application.Match(word, nameA, 0)) Then
                    matchFound = True
                    Exit For
                End If
            Next word
        End If
        
        ' Place an "X" in Column C if a match is found
        If matchFound Then
            ws.Cells(i, 3).Value = "X"
        End If
    Next i
End Sub
 
Upvote 0
See if this provides what you are seeking :

VBA Code:
Sub CompareNames()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim nameA As Variant
    Dim nameB As Variant
    Dim word As Variant
    Dim matchFound As Boolean
   
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
   
    ' Find the last row with data in Column A or Column B
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If ws.Cells(ws.Rows.Count, "B").End(xlUp).Row > lastRow Then
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    End If
   
    ' Loop through each row
    For i = 1 To lastRow
        nameA = Split(ws.Cells(i, 1).Value, " ")
        nameB = Split(ws.Cells(i, 2).Value, " ")
       
        ' Initialize matchFound as False
        matchFound = False
       
        ' Check if any word in nameA exists in nameB
        For Each word In nameA
            If IsNumeric(Application.Match(word, nameB, 0)) Then
                matchFound = True
                Exit For
            End If
        Next word
       
        ' Check if any word in nameB exists in nameA
        If Not matchFound Then
            For Each word In nameB
                If IsNumeric(Application.Match(word, nameA, 0)) Then
                    matchFound = True
                    Exit For
                End If
            Next word
        End If
       
        ' Place an "X" in Column C if a match is found
        If matchFound Then
            ws.Cells(i, 3).Value = "X"
        End If
    Next i
End Sub
 
Upvote 0
Ok so I have a college that has changed their name, they went from otero junior college to otero college. I want both to show as same school. It is on the same page of the workbook just comparing two columns with a helper in the middle as to if they matched which would be shown as 0 or if they mismatched 1.
 
Upvote 0
See if this works :

VBA Code:
Option Explicit

Sub CompareNames()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim nameA As Variant
    Dim nameB As Variant
    Dim word As Variant
    Dim matchFound As Boolean
  
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
  
    ' Find the last row with data in Column A or Column B
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If ws.Cells(ws.Rows.Count, "C").End(xlUp).Row > lastRow Then
        lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    End If
  
    ' Loop through each row
    For i = 1 To lastRow
        nameA = Split(ws.Cells(i, 1).Value, " ")
        nameB = Split(ws.Cells(i, 3).Value, " ")
      
        ' Initialize matchFound as False
        matchFound = False
      
        ' Check if any word in nameA exists in nameB
        For Each word In nameA
            If IsNumeric(Application.Match(word, nameB, 0)) Then
                matchFound = True
                Exit For
            End If
        Next word
      
        ' Check if any word in nameB exists in nameA
        If Not matchFound Then
            For Each word In nameB
                If IsNumeric(Application.Match(word, nameA, 0)) Then
                    matchFound = True
                    Exit For
                End If
            Next word
        End If
      
        ' Place an "X" in Column C if a match is found
        If matchFound Then
            ws.Cells(i, 2).Value = "1"
        Else
            ws.Cells(i, 2).Value = "0"
        End If
    Next i
End Sub
 
Upvote 0

Forum statistics

Threads
1,226,836
Messages
6,193,248
Members
453,784
Latest member
Chandni

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