Filter Massive Data by Cell Value on Specific Column

[JPWRana]

I have imported a massive amount of data from Access using excel: 43 columns and 43k rows of data on Sheet 2.

Of all that info, Column B (Log Number) has about 500 different values, where each one of those values will be in the 43k rows no more than 80 times. From there, I only need everything that is of one value.

Ex: Log Number 14-2-AGUILAD is in Column B (along with many other columns)

This value of "14-2-AGUILAD" also appears on Sheet 1 in cell A1.

I need a macro that says: Of all of the imported data I have sent to Excel from Access, filter out for ONLY what's in Sheet1!A1.

I am doing it right now using Advanced Filter, but it won't filter for just that section.

Please help?

 

[Jerry Sullivan]

Thanks for posting the updated screenshot. I overlooked handling the no-records-found scenario.

Here's a modified version that provides a msgbox and colors the structure that wasn't found in column B.

Sub DoBatchOfParamQueries()
'--Purpose is to automate the entry of series of values into
'  an existing parameter query that takes one value as a parameter.
'  After each query is run, the results are copied to the right
'  of the parameter source cell
'  This code assumes:
'     Query is already setup, tested and working manually
'     The query returns only one record. If more than one record
'     is returned the user is warned and copying those results skipped.
'     The connection's "Enable background refresh" option is unchecked

 Dim rParamCell As Range         'Query obtains parameter value here
 Dim rParamSource As Range       'Range of values to be used as parameters
 Dim rSourceCell As Range        'Current source cell being processed
 Dim tblResults As ListObject    'Table holding results of each query
 Dim sExistingParam As String    'Saves value to restore at end of process
 
 With ActiveSheet
   Set rParamCell = .Range("B2")
   Set rParamSource = .Range("B6:B" & _
      .Cells(.Rows.Count, "B").End(xlUp).Row)
   Set tblResults = .Range("C1").ListObject
 End With
 
 '--save existing value in ParamCell
 sExistingParam = rParamCell.Text

 For Each rSourceCell In rParamSource
   '--place value in ParamCell and refresh query
   '  of parameter query
   rParamCell.Value = rSourceCell.Text
   tblResults.Refresh

   With tblResults
      If .DataBodyRange Is Nothing Then
         MsgBox "No record found for: " & rParamCell.Value
         rSourceCell.Interior.Color = vbRed
      Else 'has results
         If .DataBodyRange.Rows.Count = 1 Then
         '--copy first record next to corresponding parameter source cell
           .DataBodyRange.Resize(1).Copy _
               Destination:=rSourceCell.Offset(0, 1)
         Else
            MsgBox "More than one record found. " & _
               "Re-Run this query manually after the batch " & _
               "then store desired record."
         End If
      End If
   End With
 Next rSourceCell
 
 '--restore previous Param and query results.
 rParamCell.Value = sExistingParam
   tblResults.Refresh
   
End Sub

 

[JPWRana]

Jerry, I think I may have found a way to reduce the double values but I don't think I'm putting in the right operators in MS Query.

On the sometimes (to rare) occasions that I get more than 1 result per query search, I can be able to at least filter THOSE results based on...

If Log starts with MW- or ASPL-

OR

From a Different Table, if one of the criteria is not populated.

However, when I want to add a second layer of filter in the query, I get ZERO results. Are the operators similar to MS Access?

 

[Jerry Sullivan]

MS Query uses SQL to query your database. The query you've used so far is relatively simple, and that's allowed you to use MS Query's "criteria" input fields as the user interface for creating an SQL query.

To accomplish more complex queries like the ones you've just described, you'll probably need to manually write the SQL Query instead of using the "criteria" input fields.

In the MS Query toolbar, you'll see a button labeled "SQL". If you click on that, you can see the SQL expression MS Query has made from your inputs, and then edit or paste in a more complex SQL expression.

I don't know if adding that complexity is a good idea for you this is just for rare exceptions that you get 2 or zero results- especially if this is for your own use.

 

[JPWRana]

MS Query uses SQL to query your database. The query you've used so far is relatively simple, and that's allowed you to use MS Query's "criteria" input fields as the user interface for creating an SQL query.

To accomplish more complex queries like the ones you've just described, you'll probably need to manually write the SQL Query instead of using the "criteria" input fields.

In the MS Query toolbar, you'll see a button labeled "SQL". If you click on that, you can see the SQL expression MS Query has made from your inputs, and then edit or paste in a more complex SQL expression.

I don't know if adding that complexity is a good idea for you this is just for rare exceptions that you get 2 or zero results- especially if this is for your own use.

Jerry, after a few months of using this, I do feel it's gotten to the point where I need to use SQL. I am getting more false positives enough times to annoy me. Do you happen to have a youtube video like the one you linked me that will help for that aspect of parameter queries?

 

[JPWRana]

Also, on a similar topic (same subject), when I run a parameter querry, I get 80 results (all of the same value). How do I tell the parameter query just bring me 1 value... or the 1st value?