Mister Ian's Weblog

Blog

  • Generating some buzz

    Generating some buzz

  • The site is moved to a new host!

    InterServer – Affordable Unlimited Web Hosting, Cloud VPS and Dedicated Servers

  • Excel Conditional Formatting

    How to Compare Two Columns in Excel (for matches & differences)

  • Unifi Protect Problems

    I have a new Cloud Key Gen 2 + I have the SDN part working. But can’t get protect working.

    I try on the web browser and it stalls out. See the image below.

    Then after putting in the information it freezes as the below image.

    And on the iOS app it also fails. Initially, the iOS app would time out about 3/4 of the way around the “waiting” circle. Now it goes ahead but errors. See this YouTube video of the screen capture from my phone.

  • Word to Excel Breaks

    1. In Word replace all the paragraph marks with a unique character.
    2. In Excel replace the unique character with the Excel line break character.
    3. In the Replace field, enter the following Alt code: Alt+0010. This code enters in a single line break. You will not see this character but the cursor may change.

    Note, to enter an Alt code, hold down the Alt key as you type the digits on the numeric keypad. It may help to have Num Lock on.

    Reference

  • Excel Reverse Pivot Table

    Excel VBA to reverse a Pivot Table. You can always trust finding Excel stuff if you use J-Walk as part of the search.

    Sub ReversePivotTable()
  ' Before running this, make sure you have a summary table with column headers.
  ' The output table will have three columns.
  Dim SummaryTable As Range, OutputRange As Range
  Dim OutRow As Long
  Dim r As Long, c As Long
  On Error Resume Next
  Set SummaryTable = ActiveCell.CurrentRegion
  If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
    MsgBox "Select a cell within the summary table.", vbCritical
    Exit Sub
  End If
  SummaryTable.Select
  Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
  ' Convert the range
  OutRow = 2
  Application.ScreenUpdating = False
  OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
  For r = 2 To SummaryTable.Rows.Count
    For c = 2 To SummaryTable.Columns.Count
      OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
      OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
      OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
      OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
      OutRow = OutRow + 1
    Next c
  Next r
End Sub