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.

[Tutorial] Create a folder structure in Excel

I am creating a knowledge repository for my team and I wanted to create a neat index file. The aim is to have a tree structure and the end result to be a HTML page. I have started with the HTML Kickstart framework and with some help from the internet, I am in the progress of creating a nice intranet page for the repository (I will probably add a post on my experience once complete). In the meanwhile, I need a temporary index that I can share with others so that they can access documents right away.

I decided to go the Excel way, but without macros or other complex steps. You too can create a basic tree structure for any folder to help you keep track of its contents. So if you have the folder ready and Microsoft Excel is just a double-click away, let’s get started.
1 As always start with a blank new workbook. The screenshots are from Excel 2013, but would work on pretty much every version from 2003.

2 Hide Gridlines. From the ribbon, goto View and uncheck the Gridlines box to remove the grid and be left with a blank white worksheet.
0002-Hide-Gridlines-Excel

3 Adjust Column Widths. Select fifteen columns from C through Q to 4. Here are the screenshots on how to do it.
0003-adjust-column-widths-excel

4 Navigate to the folder of choice and with the Shift Key pressed, right click on an empty area of the folder. In the contextual menu, select Open command window here.
0004-folder-right-click-windows

5 List the contents of the directory (including sub-directories) into a text file by issuing a dir /s /b > tree.txt command from the command window.
0005-capture-dir-command

6 Open the text file tree.txt (it will now be visible in the Explorer window) and copy the entire content. Paste the content into columns A & columns C of the excel worksheet.
0005-dir-result-in-excel

7 Select contents of Column A & Column C and sort them on Column C by alphabetical order.
0007-sorted-data-in-excel

8 Select all cells of Column A and change the font color to White (or the background color of the sheet if you have at some point changed it).
0009-font-color-excel

9 Split data into columns with text to columns by selecting all data in column C and the Text to Columns option on screen. The delimiter is \.
0008-delimited-text-in-excel

10 Select all the cells from column C to Q with data and we shall apply some conditional formatting to make the content look better. From the Home tab in the ribbon, select Conditional Formatting and click on New Rule. From the popup window, select “Use a Formula to determine which cells to format” and enter the following formula =AND(NOT(ISBLANK(C2)),C2=C1) Please note that I have filled data from row 2 and cell selection has started from C2
After entering the formula, click on Format and make the following changes. First, within the Font tab, set Color as White (or the worksheet background color). Next, in the border tab, give a right border of your choice. I have chosen a dotted grey line.
0010-conditional-formatting

11 Here is the resultant screen after conditional formatting.
0012-final-columns-after-formatting

12 You can hide (or even delete) the columns which contain the information upto the current folder. In my example, I was in the FirefoxPortable folder and I could hide/delete all columns that contained D: or Program Files or FirefoxPortable. It reduces clutter and makes the sheet more presentable.
0013-hide-columns-in-excel

13 Create hyperlinks in column B to navigate to the directory/file within seconds by entering the formula =HYPERLINK(A2,8) in cell B2. There is nothing magical about the number 8, it is just that it represents a mouse icon in Wingdings. You can choose your own font and your own text instead of 8
0014-make-hyperlink-in-excel

14 Finally, drag the bottom right corner of cell B2 till the last row with data. This copies the formula in cell B2 making all other cells in Column B a hyperlink.
0015-drag-and-fill-formula

There you go, a navigable Folder structure right out of Excel.

Excel Macro : Repetitive Ctrl+C (and some more)

My Excel skills are being tested quite often now. I had to work on a repetitive task to close a large list of support incidents. The input list of incident numbers were in an excel sheet and for each, I had to go to the incident management software and update some fields (primarily drop-down selections) prior to closure. As any gamer would attest, it is not much fun having to switch between two sides of the keyboard when also working with a mouse. Hence the act of Ctrl+C & Ctrl+V would be interrupted by having to use the down arrow and get to the next cell. I decided to write an Excel Macro that would go to the next cell below, select it and copy it. If you think it would be tough, think again. Here is the code.

Public Sub NextRowCopy()
 
    Dim currRow As Integer
 
    currRow = ActiveCell.Row
    currRow = currRow + 1
 
    Range("C" & currRow).Select
    Selection.Copy
 
End Sub

I pasted this in a new module in my workbook (Alt+F11 » Right Click Workbook in Project Explorer to the left and Insert » Module). As you would have guessed, the incident numbers were in column C. After saving the macro, I went back to the workbook. I pressed Alt+F8 to see the NextRowCopy macro in the popup window. Selected it and clicked on Options, assigned it a shortcut Ctrl+Q (again for the same reason that Q was to the same side of the keyboard as C).

Once I updated the Incident, I would do a Ctrl+Q and I would have the next incident number to be worked on.

The And Some More part

Most of the fields to be updated in the system were dropdown selections, but there was a standard text to be updated as well. I couldn’t copy it from excel since it would change my cursor location and I didn’t want to have to open a notepad and cycle three windows for this. I searched for a multiple clipboard manager and decided to use QuickTextPaste. It lets you assign shortcuts to predefined text, run programs and more.

My task was completed and while looking back at my macro, I think I could have had excel do the copy of the predefined text as well. Let us say that the existing data was from columns A through N. I could add the text I needed to paste in Column Q and have the macro check where I was currently and move to the right cell.

Public Sub NextRowCopy()
 
    Dim currRow As Integer
 
    currRow = ActiveCell.Row
    currCol = ActiveCell.Column
 
    If currCol = 3 Then
        ' We are in the incidents column. Switch to column O
        currCol = "O"
    Else
        ' We are in new column O, switch to column C in the next row
        currCol = "C"
        currRow = currRow + 1
    End If
 
    Range(currCol & currRow).Select
    Selection.Copy
 
End Sub

Each time the macro ran, my clipboard had either the incident to be searched or the text to be entered to close the incident. Total time saved:  30 minutes 

[Template] – Meeting Minute Taker in Excel

Slicers are a pretty cool filtering feature available in Excel 2010 and above. In a few words, slicers help you filter a table (pivot or otherwise) by simply clicking on the items you wish to see. You can create multiple slicers for a table, letting you play with data in ways which was not possible with simple Filters. Using filters was often cumbersome since you had to scroll left & right to find the right columns. Data available for filtering would be restricted to available entries from previous filters and often left you wondering which column was filtered causing your table to show partial data.

If I have you hooked on slicers, head over to the post at office.com and be sure to come back here for the meeting minutes template.

Meeting Minutes Template

The template consists of two sheets – Meeting Minutes and Team List. Some data has been pre-populated so that you can see how the slicer works. This data can be safely deleted (Select cells & hit delete, don’t delete entire rows). First, head over to the Team List sheet and enter your team members in the table. As you add more data, you will notice that the table automatically expands and you can see the alternate shading. On the Meeting Minutes sheet, you might want to replace The Science of Deduction with your own team name. Begin entering data in the table below the slicers and you are good to go.

Notes

Here are some additional points you will want to consider

  • The drop-down for Type and Status is from Data Validation. Feel free to change the list, by selecting the column in the table and modifiying the Data Validation Settings.
  • You can add more columns to the Meeting Minutes/Team List. If you add columns after the last column, make sure you the table has expanded to cover the new columns. Excel will automatically do this for you, however, if it doesn’t happen you need to pull the small blue inverted L shape on the last cell on the last row of the table
  • New slicers can be inserted easily. Select an cell within the table, a new tab should be visible in the Office Ribbon called Design (under Table Tools). Click on Insert Slicer and follow the simple instructions on screen
  • You can switch between sheets using the buttons on the left side of the sheet.

Download

Meeting Minute Tracker
Meeting Minute Tracker
Meeting_Minute_Tracker.xltx
21.9 KiB
486 Downloads
Details

Excel – Quickly color your cells [VBA Macro]

I was recently creating a 2014 calendar in excel and shading some of the cells to create a nice effect was becoming difficult. The default colors in the Office color template were quite pale and didn’t stand out. The inspiration for colors came from palettes on Adobe Kuler website. The palette gave me the RGB value as well as the Hex code. Microsoft Excel‘s custom color chooser expects you to enter the RGB values individually. I needed to update 84 (from 7 days, 12 months) unique colors and from this super set, colors would repeat across multiple cells.

Color Cells in Excel Macro

A quicker solution was needed to convert HEX codes to cell background. A macro was what I needed. My macro needed to do this; Recognize the HEX code entered in the cell and update the cell background to the equivalent color. Of course some initial cell validations need to be done. For example, a hex value 13394 is actually 013394. Some hex values are three characters long, such as ccc, which is a contracted form of saying cccccc. Here is the macro snippet

Dim ValidHEX
 
Sub change_color()
 
    ' Variable Declaration
    Dim currCell As Object
    Dim R, G, B As Integer
 
    ' Valid Hex values
    ValidHEX = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "a", "b", "c", "d", "e", "f")
 
    'Loop through each cell in the selected Range
    For Each curCell In Selection
        StrValue = curCell.Value
 
        ' Basic error checks
        ' If data is not a hex value, bypass it
        If Len(StrValue) < 3 Or Len(StrValue) > 6 Or Len(StrValue) = 4 Then
            GoTo NextIteration
        End If
        ' Check if input is valid Hex
        For i = 1 To Len(StrValue)
            If Not IsHex(Mid(StrValue, i, 1)) Then
                GoTo NextIteration
            End If
        Next
 
        ' If data preceded by single quote, drop it
        If Mid(StrValue, 1, 1) = "'" Then
            StrValue = Mid(StrValue, 2)
        End If
 
        ' For hex values such as ccc which is short for cccccc
        If Len(StrValue) = 3 Then
            StrValue = Mid(StrValue, 1, 1) & Mid(StrValue, 1, 1) & Mid(StrValue, 2, 1) & Mid(StrValue, 2, 1) & _
                        Mid(StrValue, 3, 1) & Mid(StrValue, 3, 1)
        End If
 
        ' If the entire HEX value is just numbers and the R component has a
        ' preceding 0 Excel will drop it. Pad the 0 and continue processing
        If Len(StrValue) = 5 Then
            StrValue = "0" & StrValue
            curCell.Value = "'" & StrValue
        End If
        R = CLng("&H" & Mid(StrValue, 1, 2))
        G = CLng("&H" & Mid(StrValue, 3, 2))
        B = CLng("&H" & Mid(StrValue, 5, 2))
 
        curCell.Interior.Color = RGB(R, G, B)
 
        ' Uncomment the following if you want the hex value removed
        ' after processing. Saves you effort of clearing the cells
        ' curCell.Value = ""
 
NextIteration:
    Next
 
End Sub
 
Function IsHex(char2Check)
 
    Dim j
    For j = 0 To UBound(ValidHEX)
        If ValidHEX(j) = char2Check Then
            IsHex = True
            Exit Function
        End If
    Next
    IsHex = False
 
End Function

Save your workbook as an Excel Macro-Enabled Workbook. To execute the macro, click on the Developer tab and the Macros icon. From the next pop-up window, choose the change_color macro and click Run. The cell color has changed.

You can also assign a shortcut key to execute the macro by selecting the macro name as before, but click on Options instead of Run. You can choose a shortcut key for this macro in the next window.

Planning leaves for your team – Excel Template

Vacationing Woes for the ManagerRecently, the Project/Resource estimation template crossed over the 1000 download mark. Thanks to all you folks who used the template to plan and manage your projects. I thought I would commemorate that with another Team Planning Template – this time to plan leaves and Holidays.

When your team member plans for a vacation or the holiday season is just round the corner, you need to rework your schedule and maybe the delivery dates. Sometimes it is not clear how many team members are not available on a particular day, since each person may talk to you about their leave plans on different dates. The Leave Planner template gives you good control over your team’s leaves.

Some key features of the Leave Planner are:

  • Track upto 50 team members in one place. You can even distinguish between sub-teams
  • Customize your work week. Does the team work Tuesday through Saturday? No problem, this planner can handle it
  • Track Leave status changes from “Applied” to “Approved” or “Rejected”
  • Enter holidays upfront so you can schedule the team’s work accordingly. Plus you can easily see when there will be a deluge of leave requests (around long weekends)
  • Create Comp-offs, see Unplanned leaves and much more

The template has a “How-To” sheet that guides you through the process of setup and using the leave planner. Download link follows the screenshots.

The sheet works in Excel 2007 and above. Using the template in an earlier version can lead to undesirable results due to limitation of conditional formatting in older versions of Excel

Screenshots

Leave Planner - How to Guide
The inbuilt How-to Guide gets your started and answers all your queries
Leave Planner - Control Sheet
The Control sheet lets you setup parameters such as Holidays, Work week and also the team details
Leave Planner - Master Sheet
Dashboard of the leaves. Add or update leave plans
Leave Planner - Summary Table
The top summary section gives you an update on the latest leave counts for your team members
Leave Planner - Color coded days
Easy color coding lets vacation days stand out

Download

Leave planner for the team
Leave planner for the team
Team-Leave-planner-thecodeisclear.zip
74.3 KiB
1923 Downloads
Details

Estimate your Project and Resources with Excel

Forget fancy programs and tools, your dear old Excel (even the plain-jane 2003 version) can provide you excellent planning options for your project. Having worked with multiple projects and across geographies, it is often important to plan the project to the level of each day. You can use this template to handle your project tasks by drilling down to the minute level of day-to-day tasks. Plan for resources who are spread across locations and also estimate the cost required for the project using parameters.

To use the estimation sheet, open the Excel template and update the parameters in the sheet “Parameters”. Instructions are available on the page on mandatory fields and explanation of each parameter. Once you are done, move on to the second Sheet “Resource Loading” and start entering your tasks along with the effort involved.

This template contains no macros and has been tested in Microsoft Excel 2003 & Excel 2007. If you enjoyed using this sheet or need a quick modification, drop me a comment.

Project Tracker Xlt
Project Tracker Xlt
Project_Tracker.xlt.zip
63.4 KiB
466 Downloads
Details
 Thanks to our reader Arvin for identifying an issue with the offshore rate computation. This has been fixed.