Raw Data Processing: Identifying Entities

If the County saves both individual owner names as well as entities under a single Property Owner field then one of the data processing steps you will need to conduct is to programmatically identify these entities and move them to the ‘Company’ column within your list. You will also need to indicate under the ‘Type’ field that these records are companies by entering the value of ‘Company’ for these records.

Below is a list of common keywords that would indicate that the owner is not an individual but rather a company or entity:

‘
acquisition
aka
assoc
associa
association
bank
benefactor
beneficiary
business
c/o
care of
church
city
club
county
dept
develop
eastern
es
est
estate
et al
family
firm
foundation
holding
inc
invest
ira
land
llc
lp
ltd
northern
of
owner
ownership
partner
property
school
service
southern
st
state
tenant
tr
trs
trust
univ
us
western

Raw data lists also contain multipart names or prefixes and suffixes that need to be identified and properly formatted into the ‘First Name’ and ‘Last Name’ columns in the case of ‘Individual’ records and should not be treated as companies simply because they contain multiple spaces. You can also use this list to distinguish between common multipart names and middle initials that the county may also include in the raw data and which should be removed.

Below is a list of common multipart names, prefixes, and suffixes that you should account for in your data processing scripts:

Atty
Da
De
Del
Della
Des
Di
Dr
Du
Esq
Et Al
I
II
III
IV
Jr
La
Le
Mac
Mc
MD
Mr
Mrs
O
PhD
Prof
Ret
Rev
Sir
Sr
Von

Below is an example macro that you can use (and adjust as needed) that identifies companies or multipart names based on keywords lists like the ones posted above. We use similar scripts to identify the Companies in a list and move the company name to the ‘Company’ column in our list.

First, create a workbook with 3 sheets:

    Set InfoWs = Sheets("info")
    Set Kw = Sheets("keywords")
    Set SmpleWs = Sheets("sample")

The “info” sheet must have a title in column “A”, where you paste the OWNERS on row 2. The “keywords” sheet starts in Column “A” row 1, and the data will be on the “sample” sheet row 2, so the titles for the “sample” will be “Type”, “First Name”, “Last Name” and “Company”.

Then add and run this macro to identify records based on the keywords listed in your “keywords” sheet:

Private Sub GetCompanies_Individuals()
    Dim wrdLRow As Long
    Dim wrdLp As Long
    Dim OwnersLrow As Long
    Dim OwnersLp As Long
    Dim fndWord As Long
    Dim InfoWs As Worksheet
    Dim Kw As Worksheet
    Dim SmpleWs As Worksheet
    Dim names() As String

    On Error Resume Next

    'Define worksheet that has data on it....
    Set InfoWs = Sheets("info")
    Set Kw = Sheets("keywords")
    Set SmpleWs = Sheets("sample")

    'Get last row for keywords based on column A
    wrdLRow = Kw.Cells(Rows.Count, "A").End(xlUp).Row
    
    'Get last row for owners based on column A
    OwnersLrow = InfoWs.Cells(Rows.Count, "A").End(xlUp).Row

    'Loop through lists and find matches....
    For OwnersLp = 2 To OwnersLrow
        fndWord = 0
        For wrdLp = 1 To wrdLRow

            'Look for word...
            fndWord = Application.WorksheetFunction.Search(Kw.Cells(wrdLp, "A"), InfoWs.Cells(OwnersLp, "A"))
            'If we found the word....then
            If fndWord > 0 Then
                    SmpleWs.Cells(OwnersLp, "A") = "Company"
                    SmpleWs.Cells(OwnersLp, "D") = WorksheetFunction.Proper(InfoWs.Cells(OwnersLp, "A"))
                    'fndWord = 0
                Exit For
            End If
        Next wrdLp
        
        If fndWord = 0 Then
            names = VBA.Split(InfoWs.Cells(OwnersLp, "A"), " ")
            SmpleWs.Cells(OwnersLp, "A") = "Individual"
            SmpleWs.Cells(OwnersLp, "B") = WorksheetFunction.Proper(names(0))
            SmpleWs.Cells(OwnersLp, "C") = WorksheetFunction.Proper(names(1))
        End If

    Next OwnersLp
End Sub

NOTE: If you are not familiar with creating macros, data processing software, data processing techniques, and best practices then we highly suggest you hire a professional data processor to filter and process your raw data lists to be used for import. This article explains how you can easily outsource this step to a highly qualified professional.