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.

Filtered Data

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

Join Tables

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

Select sources: Select the source ranges for the two tables to join.

Matching columns: You can select one or more columns to match against when joining two worksheets.

Output columns: You can select which columns that you want to output and choose the output destination.

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

Select sources: Select the source ranges for the two tables to merge.

Matching columns: You can select one or more columns to match against when merging two worksheets.

Output options: Choose table merge options, including whether to append new rows and/or merge changes.

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

Table 1: Newsletter subscribers.	Table 2: Unsubscribers.	Find Unmatched Result: New list of subscribers with unscribers removed.
Select sources: Select the source ranges for the main table and table with values to exclude.	Matching columns: You can select one or more columns to match against for row by row comparisons.	Output options: Select the output options for the Find Unmatched reults.

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.