Spreadspeed Feature Tour
Excel Table Query Tools
The Spreadspeed Table Query tools let you join tables, update (merge) tables, find unmatched rows, remove hidden rows, and extract duplicated or filtered data. They are very flexible and allow you to work on any type of range.
In most cases the Spreadspeed table query tools operate on any ranges. The ranges do not have to be formatted as Excel tables, or even have heading rows. The exception is when you want to perform a table query and extract rows that meet a set of criteria. The row filtering tools for Excel tables are excellent, so the table query tools are designed to leverage them and operate on the visible rows of filtered data (when applicable).
The Join Tables tool lets you combine the columns from two worksheets to create a new table. It supports three join types - exact match, all the values from table 1 and only the matching values from table 2, or all the values from table 2 and only the matching values from table 1. Through a three-step wizard, you can select two tables (worksheet ranges), choose which columns to match on, and then select the columns that you want output to a new worksheet.
Join Tables Screenshots
Merge Tables (Update and Append)
The Merge Tables tool merges the changes in one table into another. You can choose to merge updated cells as wll as append new rows to the main table. Plus you can opt to mark which cells and rows were updated. The merge tool also has options to skip cells with formulas, cells with blanks, do case-insensitive comparisons, and check for differences in number and date formats. You can set color codes to indicate each type of update, and create a column listing the changes made to each row.
Example of a merged table with updated cells highlighted
in yellow and new rows highlighted in green.
Merge Tables Screenshots
Find Unmatched Rows
The Find Unmatched Rows tool queries two tables and finds all the values from one table that do not exist in another. The best way to explain the usefulness of this tool is by example. The first image below left shows a list of email newsletter subscribers. The second image shows a list of unsubscribers. The Find Unmatched Rows tool was used to create a new list with the unscribers removed, as shown in the thrird image.
Find Unmatched Rows Screenshots
Extract Filtered Data from Table
The Extract Filtered Data tool is designed to work with the Excel table filtering tools. Given a table with a filter applied, it will iterate over the table and extract the values to a new worksheet, leaving the existing table intact.
Extract Columns from Table
The Extract Columns tool is essentially a shortcut method to copy a range to a new worksheet, delete the unwanted columns, and convert the formulas to values (optional - in case the formulas refer to columns that are to be deleted).
Extract Duplicate Data from Table
Excel has a built-in tool to delete duplicates, and using the Advanced Filter or Conditional Formatting there are several tricky ways to highlight duplicate values. However, sometimes instead of deleting them or just highlighting them, you want to extract the the duplicates, which is where the Extract Duplicate Data tool comes in handy. it allows you to select up to three columns to sort the table, check above and below for duplicates, and then extract them to a new worksheet.
Remove Hidden Rows from Table
The Remove Hidden Rows tool is designed to operate either on an Excel Table that has filtering applied or a normal range with hidden rows. As the name implies, it will iterate over the rows in a table and delete any row that is hidden.