Here is the scenario, the boss comes in to you, and wants you to add the new cities you all did business in last month to your master list.
You are to send the new list to the marketing department, so you all can market to the new cities you did business in last month.
The “New List” has some new cities not in the “Master List”. Your job is to add only the new entries to the “Master List”.
Here’s what the list looks like:
“How do I compare two columns in Excel?”
Here’s a way how you do it with VBA.
1. We are going to set the “Master List” as a named range.
—>BTW, if you need to delete or edit your named range, you can use the “Formulas” > “Name Manager” to correct your error.
Follow the following image:
Here is the process:
1 Set cells A2:A37 into a named range
2 Set up a user defined function which will take the value of the “New List” and check if the value in the “New List” is already in the “Master List”.
How do you know if two cells match in Excel?
This will identify the new cities and now we can filter them and add them to the “Master List”.
Here’s the code:
'by erik@loeblcomservices.com - 2019 Function GetNewEntries(Arg1) As Boolean 'Purpose: Find the new cities we did business in Dim cnn As Object Dim rst As Object Dim strSQL As String Dim lngCount As Long Dim strValue As String Dim blnNew As Boolean 'Set up the connection to the Excel worksheet Set cnn = CreateObject("ADODB.Connection") With cnn .Provider = "Microsoft.ACE.OLEDB.12.0" .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" .Open End With 'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name. 'get the new list cell value strValue = Arg1 'check if that value is already one we've done business in. strSQL = "SELECT * FROM [MasterList] WHERE [Master List] ='" & strValue & "'" Set rst = cnn.Execute(strSQL) If rst.EOF Then 'new item blnNew = True Else 'already in the Master List blnNew = False End If rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing GetNewEntries = blnNew End Function
Watch how it’s done:
Let me know if you have any questions.
****************************************************
|