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.

EXCEL

  • 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)

EXCEL MACROS

  • Page Break Macro

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

ActiveSheet.ResetAllPageBreaks

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)
Else
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) = _
Application.Transpose(dc.keys)
Sheets(4).Range(“F2”).Resize(UBound(dc.items) + 1) = _
Application.Transpose(dc.items)

Set dc = Nothing
End Sub

SPSS

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

SQL

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

GITHUB (BASH) 

QGIS

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

ZIPPING MULTIPLE FOLDERS AS SEPARATE FILES (BATCH)

  • 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 comment