Quick Reference

Following are snippets of codes, syntax and formulas that come in handy from time to time. Only brief descriptions have been included but can be expanded if there is interest. If you have something you would like to share, please feel free to forward it to me (Chris at CConley dot ca) and I will post it here.


  • Data Cleaning:
  • Select and fill empty cells:
    • F5 > Special >blanks >ok
    • create your relative formula (“=” and the cell above, below or behind you want to use as a constant)
    • Apply the formula to all the selected/empty cells: ctrl-enter
    • Note: this is a real time saver when you are working with excel tables that have a lot of merged cells that need to be reformatted
  • Replace comma with alt+enter:
    • Find ,
    • Replace  (hold Alt and type) 010
  • Remove decimal places: =ROUNDDOWN(cell,0)
  • Copy visible cells: click on Home > Edit > Find/Select > Go To > Special > Visible
  • Select the first value in a space delimited cell:  =LEFT(A1,FIND(” “,A1)-1)
  • Drop first value of space delimited cell: =MID(A1, FIND(” “,A1&” “)+1,255)
  • Drop the last value of space delimited cell:
    =LEFT(E6,LOOKUP(2^15,FIND(” “,E6,ROW(INDIRECT(“1:”&LEN(E6)))))-1)
  • Dates
    • Extract Month from Date of Birth: =Text(cell,”mmm-dd-yy)
    • Extract Year from a date value: Year(cell)
  • Add only negative numbers: =SUMIF(range, “<0”)
  • Identify the quartile of a value: =SUMPRODUCT(–(cell >QUARTILE(cellrange, {1,2,3,4})))+1
  • Number records by their sorted order: =COUNTIF($D$2:D2,D2)
  • Slope of Line of Best fit: =INDEX(LineST(ycoordinate, xcoordinate),1)


  • Page Break Macro

Sub PageBreak()
Dim CellRange As Range
Dim TestCell As Range


Set CellRange = Selection
For Each TestCell In CellRange
If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakManual
End If
Next TestCell
End Sub

  • VLOOKUP (Removing Error Codes from empty lookups)

=IFERROR(TRIM(VLOOKUP([value to lookup],table containing all the info to compare to, column # of the value you want returned,FALSE)),””)  <- FALSE uses exact values.  TRUE uses approximate values

CONCATENATE VALUES (2 column long table format): from http://tinyurl.com/npql9pd

Option ExplicitSub groupConcat()
‘– data needs to be in 2 columns, long table format

Dim dc As Object
Dim inputArray As Variant
Dim i As IntegerSet dc = CreateObject(“Scripting.Dictionary”)

‘–change Sheet (number not name) and Range below to applicable
inputArray = WorksheetFunction.Transpose(Sheets(4).Range(“A2:B1717”).Value)’– assuming you only have two columns – otherwise you need two loops
For i = LBound(inputArray, 2) To UBound(inputArray, 2)
If Not dc.Exists(inputArray(1, i)) Then
dc.Add inputArray(1, i), inputArray(2, i)
dc.Item(inputArray(1, i)) = dc.Item(inputArray(1, i)) _
& “,” & inputArray(2, i)
End If
Next i’–output into sheet
Sheets(4).Range(“E2”).Resize(UBound(dc.keys) + 1) = _
Sheets(4).Range(“F2”).Resize(UBound(dc.items) + 1) = _

Set dc = Nothing
End Sub


  • Concatenate:
    • String fullname(A20)
    • Compute fullname = CONCAT (Field 1, Field 2)


  • Selecting by Date Range:
    • WHERE BIRTH_DATE >= ’01-JAN-90′
      AND BIRTH_DATE <= ’31-DEC-90′



  • Attaching polygon attributes to points:
    • Processing> Toolbox> SAGA> Shapes – Points> Add Polygon Attributes to Points


  • download 7-zip
  • copy and paste the following into notepad and save it as a .bat file:for /d %%X in (*) do “c:\Program Files\7-Zip\7z.exe” a -mx “%%X.zip” “%%X\*”
  • Copy the .bat file into the directory containing the sub-directories you want to compress
  • Double click the .bat file and a command prompt window will open showing you the progress in the creation of each .zip file for each sub-directory.  The zip file will have the same name as the sub-directory

Things I Routinely Forget

  • How to move a window that is stranded on a non-existent second monitor:
    • Alt-Tab to the window
    • Alt-Space to select
    • M to select move
    • use cursor keys to move the window over
  • Merging multiple csv files into a single file with the Windows Command Prompt:
    • Place all csv’s into a single directory
    • copy c:\directory_path_containing_files\*.csv c:\NewAggregatedFile.csv
  • Adding leading zeroes to an OEN:
    • DF$OEN <- formatC(DF$OEN, width = 9, format = “d”, flag = “0”)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s