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?

 

[JPWRana]

Sure! No problem! Here is the screenshot.

The top row... just under structure is my parametrized query. The results of that query are what's under the yellow headers (C3:G3).

What I want to do... see how I have a list of 12 structures? Right now I copy and paste the results from what I input in B3. What I would like to do is extend the current data link so that I don't have to copy results and paste them just below (Rows 6-17).

Instead, just like how I type in on B3 gets me results in C3:G3, I want it like an array... where I simply type in B4 and I get the results in C4:G4, B5 will get me results in C5:G5....

Right now when I attempt to do this, I get 2 rows... 1 for the header, and 1 for the results... just like you see it on C2:G3. I want to eliminate the header row.

Side Note: On very very very few times, I get MORE THAN 1 row of results... In order for me the get just 1 row of data I need... how would I go back to MS Query and say, if more than 1 result, only give me results that have Log Number CONTAINING a -13- OR -14-... that way I get the 1 result I want.

I hope my explanations don't confuse you.
Thank you for your help.

 

[Jerry Sullivan]

Is using VBA an option for you?

 

[JPWRana]

Sure... by VBA you mean macros... right?

Sorry for the delayed response.

 

[JPWRana]

Is using VBA an option for you?

Jerry, VBA is an option.

 

[Jerry Sullivan]

Below is a macro you can try. It references the ranges shown in your screenshot. If the locations of the ranges are different, you'll need to adjust those in the code.

The macro could be written to copy the desired record based on "Log Number CONTAINING a -13- OR -14-" however since it sounds like you very rarely have a query return two values I'd suggest you not add that complexity to the code. This macro will warn you if two rows are returned and you can re-run just that one item manually. Alternatively, you could modify your query to return only one record using that second criterion.

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

 With ActiveSheet
   Set rParamCell = .Range("B3")
   Set rParamSource = .Range("B6:B" & _
      .Cells(.Rows.Count, "B").End(xlUp).Row)
   Set tblResults = .Range("C2").ListObject
 End With

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

   With tblResults.DataBodyRange
      If .Rows.Count > 1 Then
         MsgBox "More than one record found. " & _
            "Re-Run this query manually after the batch " & _
            "then store desired record."
      Else
      '--copy first record next to corresponding parameter source cell
         .Resize(1).Copy Destination:=rSourceCell.Offset(0, 1)
      End If
   End With
 Next rSourceCell
   
End Sub

Make sure that your connection's "Enable background refresh" option is unchecked so that VBA waits for each query to be completed before continuing.

 

[JPWRana]

I get the following error:

Run-time error '1004':
This operation cannot be done because the data is refreshing in the background.

When I click on debug, it highlights row tblResults.Refresh under the For Each rSourceCell In rParamSource section

 

[Jerry Sullivan]

Did you do this...

Make sure that your connection's "Enable background refresh" option is unchecked so that VBA waits for each query to be completed before continuing.

 

[JPWRana]

Jerry, after disabling it, the macro runs, but I still get errors and a few weird things going on.

B3 keeps on changing value to the cell below it.
Also, for some reason, the value that I get from C2 to G2 from what is ORIGINALLY on B2 gets copied over and over every time the B3 value changes. That row of values (C2:G2) starts to copy from row 6 on until I get an error message again.

Any ideas?

 

[Jerry Sullivan]

I've modified the code to save the existing value in B3 so that value and its corresponding query results can be restored after the batch process.

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("B3")
   Set rParamSource = .Range("B6:B" & _
      .Cells(.Rows.Count, "B").End(xlUp).Row)
   Set tblResults = .Range("C2").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.DataBodyRange
      If .Rows.Count > 1 Then
         MsgBox "More than one record found. " & _
            "Re-Run this query manually after the batch " & _
            "then store desired record."
      Else
      '--copy first record next to corresponding parameter source cell
         .Resize(1).Copy Destination:=rSourceCell.Offset(0, 1)
      End If
   End With
 Next rSourceCell
 
 '--restore previous Param and query results.
 rParamCell.Value = sExistingParam
   tblResults.Refresh
   
End Sub

If you are still having difficulties, please post a screen shot like the one you have in Post #11 to help explain the problem. - that helps a great deal.

 

[JPWRana]

Jerry, I took a second look at the first macro formula you gave me, and I noticed that from the screen shot I gave you, I modified my cells, so of course the cells were not going to line up.

I did have some structures that had more than 1 record and it did give me the pop-up window you put in the macro.
However, I also ran into the issue that when the structure WAS NOT IN THE DATABASE, the entire macro gave up on me. Is there a way to have the macro skip it with a pop-up window saying not in database or red flagging that structure from the B:B where it will flag them for me to find out WHY they aren't in the database?

Here is the screen shot you requested.