Many needles in a haystack – The excel solution

I am sure you have received a huge table of data in excel and have to search for a few keywords (not just one). As an example, let’s say you receive a huge list of employee names, ids etc from HR of employees who need to complete a particular training. You want to identify if any of your team members is on that list. Even with a small 10 member team, searching becomes a chore. Here is how to create a simple macro that can be invoked anytime to perform a looped search.

Setup

Open a new excel workbook. Save the workbook as an Excel Macro-Enabled Workbook. The workbook will get saved with a .xlsm extension. Press Alt+F11 to open the VBA IDE. You will see the Project Explorer to the left. Right click on Microsoft Excel Objects, choose Insert from the menu and select Module. We will be adding our code to this module

01-sheet-setup-excel

Now back to the excel sheet (grid). There is not much setup to do here; Our macro will work with any selection irrespective of the column/row. In any case, if you want to format and beautify this, go ahead and do so.

02-sheet-formatting-excel

On to coding. Double click the Module (most likely called Module1) and add the following code

Option Explicit
 
' MACRO to perform looped find
Public Sub LoopedFind()
 
    ' How to use
    '------------
    ' 1> Select the range of cells which need to be searched
    ' 2> Invoke the LoopedFind macro (Alt+F8 -> Select LoopedFind)
    ' 3> Navigate to the folder where the excel sheet to be searched is located
    ' 4> Results will appear as new columns in the same sheet
 
    Dim FileName As Variant
    Dim SrcWkbook As Workbook
    Dim SrcWksheet As Worksheet
    Dim DestWksheet As Worksheet
    Dim RangeSource As Range
    Dim Cell As Range
    Dim FoundCell As Range
    Dim SrcFilePath As String
    Dim SrcFileName As String
    Dim SrcWksheetName As String
    Dim RowNum As Long
    Dim lColumn As Long
 
    Set RangeSource = Application.Selection
 
    Set DestWksheet = ActiveWorkbook.ActiveSheet
 
    ' Show File Open window with only xls files. If you are
    ' looking for .xls (not the newer fancy .xlsx ones)
    ' changes below
    FileName = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xlsx), *.xlsx", _
        FilterIndex:=1, _
        Title:="Select a Workbook")
 
    If FileName = False Then Exit Sub
 
    Application.DisplayStatusBar = True
    Application.StatusBar = "Macro started"
 
    Set SrcWkbook = Workbooks.Open(FileName:=FileName)
 
    For Each SrcWksheet In SrcWkbook.Sheets
 
        SrcFilePath = SrcWkbook.Path
        SrcFileName = SrcWkbook.Name
        SrcWksheetName = SrcWksheet.Name
 
        For Each Cell In RangeSource
 
            ' Identify the last column with data for the current row
            lColumn = DestWksheet.Cells(Cell.Row, Columns.Count).End(xlToLeft).Column + 1
 
            Application.StatusBar = "Currently Finding : " & Cell.Value & " in " & SrcWksheet.Name
 
            Set FoundCell = SrcWksheet.Cells.Find(what:=Cell.Value, _
                    LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
 
            If Not FoundCell Is Nothing Then
                RowNum = FoundCell.Row
                DestWksheet.Cells(Cell.Row, lColumn).Value = "› " & SrcWksheetName & " » " & FoundCell.Address
 
            End If
            Set FoundCell = Nothing
 
        Next Cell
 
    Next SrcWksheet
 
    SrcWkbook.Close Savechanges:=False
 
    Application.StatusBar = False
 
End Sub

I won’t go too much into details, but here is the basic algorithm.

03-sample-data-excel

  • Allow user to select the file which needs to be searched in (your haystack basically)
  • Loop through each sheet in the user selected workbook
  • For each cell in the selected source, find if the value exists in the sheet of the opened workbook
  • If match found, populate the next available column beside the cell value being searched (needle) with the worksheet name and cell address

Save the workbook. Add some data to test. I have put in names of some cricketers in my workbook like the image to the right. To search, I select the data and press Alt+F8. This brings the list of available macros. Once you choose "LoopedFind", the code begins executing and you can choose the file with the large amount of data. The final results are here to see.

04-output-and-source

DestWksheet.Cells(Cell.Row, lColumn).Value = "› " & SrcWksheetName & " » " & FoundCell.Address

This line above is the code that outputs the worksheet name and address. However, you can change this to suit your purpose. If you need a specific data retrieved you can manipulate FoundCell.Address to reference another column in the row where data is found and get its value. (Somewhat like a Dynamic VLOOKUP that picks up the filename at runtime)

I wish I had taken some effort to put this piece of code earlier. Not just with names, ids, I see this sheet being useful in a number of places. For one, whenever we work on a release in the mainframe environment, we create an impact analysis report. It is usually a mammoth sheet with the list of all copybook changes and the impacted programs. Out of the 1000 rows, my team would be responsible for a smaller fraction of programs. Instead of manually checking my list of programs against the IAR, I could perform this looped find and quickly know how many my team’s programs are impacted.

Hope you found this useful. Share your thoughts and feedback through the comments section.

Leave a Reply

Your email address will not be published. Required fields are marked *