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.