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:
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:
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.