The problem

This add-on solves the problem joining two sheets of data on multi-column keys that are not 100% equal, for example names in one set might using middle initial and the other not. And dates might be off with hours (think ecom server processing a payment a few hours after submitted)

Let's call these two sheets LARGE and SMALL.

SMALL is missing some of the columns in LARGE and there are no unique identifers in each sheet.

You want, in SQL terms, do somewhat of an inner join (to a new sheet we call JOINED) between the two sets and then copy the matching data from LARGE.

Sample

A real world example, LARGE could be all people signed up for something. SMALL could be an export from your payment company.

LARGE might not have a unique identifer for people, you only have name and address.

Neither SMALL does have a unique identifer for people, you only have name and address., column names might not be the same in LARGE and SMALL.

Let's say you want to copy some data for each person in SMALL that also exists in LARGE, for example the DEPARTMENT column = the purpose of this app!

LARGE and SMALL can be in same spreadsheet or in external CSV files or other Google Sheets. You can even paste tabular data as LARGE or SMALL.

Steps

These are the steps you will take (after launching the add-on):

1. Import the LARGE dataset - can be data in one of the sheets in the spreadsheet or come from somewhere else
2. Import the SMALL dataset - can be data in one of the sheets in the spreadsheet or come from somewhere else
3. Define which columns in each sheet make up the unique key so that data can be matched.
   You will for example pick NAME from the LARGE sheet and "map" it to the CUSTOMER column in the SMALL sheet, and pick ADRESS from the LARGE sheet and "map" it to the CUSTADDR column in the SMALL sheet.
4. You then click the JOIN button and a new dataset is created.
5. Finally you choose how to export the new dataset, see below

Comparer functions

Real life data might not be perfect, for example one dataset might be using a middle name for people and others might not. Dates might differ in hours etc.

This is solved by assigning a comparer function to each column map. The following functions are supported:

• Equal - the default, values need to be identical
• Equal (case insensitve) - case doesn't matter
• Equal (space insensitve) - white space and case doesn't matter
• Equal (first and last) - checking only first and last word
• Round - equal if rounding numbers
• Floor - equal if flooring numbers
• Ceiling - equal if ceiling numbers
• Same date - equal if excluding time
• Same date within 24 hours
• Same date within 12 hours
• Same date within 6 hours
• Same date within 1 hour

Exporting

You can export joined datasets in a few ways:

• Send to a NEW sheet in the open spreadsheet
• Create a new Google Sheet spreadsheet
• Save as a CSV file to your hard drive
• Copy as tabular data to the clipboard, you can then paste this data into for example MS Excel

More

Check out this short YouTube video.

Note - if you have installed the add-on and the add-on submenu only contains the 'Help' option - PLEASE RELOAD THE DOCUMENT - and the other options will appear.

Please report bugs here or reach out 2 me with questions @ support@backlund.org.

Follow me at Instagram @bjornsplayground and check out more videos at my YouTube channel.

You can view our privacy policy here.