Another Ruby and Excel Tip

Producing a list of all the items that are in one column and not another is a bit of a bear in Excel. You'd either have to use vlookup and sort for N/A or write a Macro. Thanks to Ruby's excellent Array handling facilities it is really easy in Ruby. Most of the script below is concerned with getting the data into Ruby for processing.

require "win32ole"
excel = WIN32OLE.connect("Excel.Application")

# change the row and column numbers of source and destinations to suit your Excel Spreadsheet.
# Could bring these in as parameters if preferred
ws = excel.worksheets("Sheet1")

first_col = excel_column_to_a(2, 1, ws)
second_col = excel_column_to_a(2, 2, ws)
a_to_excel_column(first_col - second_col, 2, 3, ws)
a_to_excel_column(second_col - first_col, 3, 3, ws)


def a_to_excel_column(arr, row_number, col_number, sheet)
  arr.each do |cur_val|
    sheet.cells(row_number, col_number).value = cur_val
    row_number += 1
  end
end

def excel_column_to_a(row_number, col_number, sheet)
  a = []
  # Will stop when .value of cell is nil
  while cur_val = sheet.cells(row_number, col_number).value
    a << cur_val     row_number += 1   end   a end