Automatic "Transfer" Matcher with Clickable Links

alan.heatherley · October 26, 2023, 4:48pm

Overview

This solution creates a clickable hyperlink between matching transfer transactions. It tries to automatically match the transactions, but allows you to manually set the match if necessary.

Installation

Here’s a quick video I recorded to walk through installation and usage of this solution: https://www.youtube.com/watch?v=naurGlkSOd8

There are three main steps to creating this solution and the order of the steps is important.

Download the (Helper Data worksheet), and move the Transfer Helper Data worksheet into your Tiller spreadsheet.
Add two columns to the Transactions table on the Transactions sheet
A. Transfer ID column - add this column to the far right of the table and enter the following formula into the first cell:

=IFERROR(
   IF(VLOOKUP([@Category],HSTACK(Categories[Category],Categories[Group]),2,FALSE)=xfer_group_name,
      CONCAT([@Amount],IF([@Amount]>=0," to "," from "),[@Account]," ",YEAR([@Date]),MONTH([@Date]),DAY([@Date])," (",RIGHT([@[Transaction ID]],xfer_transID_chars),")"),
      ""),
   ""
)

B. Transfer Link column - I added this column between the Amount and Account columns, but you can insert it anywhere you want. Add the following formula to the top cell:

=IF([@[Transfer ID]]="",
   "",
   IFERROR(
      HYPERLINK("#Transactions!"&
         ADDRESS(
            MATCH(
               VLOOKUP([@[Transfer ID]],
                  HSTACK(
                     xfer_manual_override[Transfer ID],
                     xfer_manual_override[Matched With]),
                  2,FALSE),
               [Transfer ID],0)+1,
            xfer_hyper_start_col)&":"&
         ADDRESS(
            MATCH(
               VLOOKUP([@[Transfer ID]],
                  HSTACK(
                     xfer_manual_override[Transfer ID],
                     xfer_manual_override[Matched With]),
                  2,FALSE),
               [Transfer ID],0)+1,
            xfer_hyper_end_col),
         xfer_hyper_friendly),
      IFERROR(
         HYPERLINK("#Transactions!"&
            ADDRESS(
               MATCH(
                  VLOOKUP([@[Transfer ID]],
                     HSTACK(
                        xfer_manual_override[Matched With],
                        xfer_manual_override[Transfer ID]),
                     2,FALSE),
                  [Transfer ID],0)+1,
               xfer_hyper_start_col)&":"&
            ADDRESS(
               MATCH(
                  VLOOKUP([@[Transfer ID]],
                     HSTACK(
                        xfer_manual_override[Matched With],
                        xfer_manual_override[Transfer ID]),
                     2,FALSE),
                  [Transfer ID],0)+1,
               xfer_hyper_end_col),
            xfer_hyper_friendly),
         IFERROR(
            HYPERLINK("#Transactions!"&
               ADDRESS(
                  MATCH(
                     VLOOKUP([@[Transfer ID]],
                        HSTACK(
                           INDEX('Transfer Helper Data'!$O$2#,,6),
                           INDIRECT("'Transfer Helper Data'!$U2:$U"&ROWS('Transfer Helper Data'!$O$2#)+1)),
                        2,FALSE),
                     [Transfer ID],0)+1,
                     xfer_hyper_start_col)&":"&
                  ADDRESS(
                     MATCH(
                        VLOOKUP([@[Transfer ID]],
                           HSTACK(
                              INDEX('Transfer Helper Data'!$O$2#,,6),
                              INDIRECT("'Transfer Helper Data'!$U2:$U"&ROWS('Transfer Helper Data'!$O$2#)+1)),
                           2,FALSE),
                        [Transfer ID],0)+1,
                     xfer_hyper_end_col),
                  xfer_hyper_friendly),
            HYPERLINK("#'Transfer Helper Data'!F"&COUNTA('Transfer Helper Data'!F:F)+1,xfer_need_help_message))
      )
   )
)

Follow these instructions to relink the formulas from external references to your local workbook.

Setup

You should be able to use it right out of the box, but there are some settings on the Transfer Helper Data sheet to customize how it works

Days Before and Days After are used to tell the Auto function how far to look for possible transfer matches. You can make these numbers smaller or larger as necessary.
Transfer Group Name - set the name of the Group that all of your transfer categories are a part of
“Need help” message - this is the message the solution displays when it can’t auto detect the matching transfer
Hyperlink Start Column and End Column - when you click the link, it selects a few columns of the row, use these values to define how much of the row is selected
Hyperlink friendly name - the text displayed on the hyperlink
Opening List Divider and Closing List Divider - in the dropdown list of the Manual Override column, these values are used to help highlight the start of the unmatched and matched parts of the list
Chars of TransID to include - this defines how many characters of the Transaction ID are used to make the Transfer ID unique

Usage

After setting up the Transaction table columns and importing the Transfer Helper Data sheet, you should see the Transfer Link column populated with links. If the Auto function identified a matching transfer, clicking the link will take you to that row. If the Auto function couldn’t find a matching transfer, clicking the link will take you to the Transfer Helper Data sheet where you can manually select a matching transfer from the drop down list. Once the matching transfer is selected, the hyperlink on the Transaction table will update to use the manual value.

Permissions

Is it ok for others to copy, use, and modify your workflow?

Sure!

Notes

Anything else you’d like people to know?

This is a first attempt at this, so let me know what does/doesn’t work for you. Thank you!

FAQ

Why might the Auto function fail to find a match? There are a couple of reasons it might fail:

More than one transfer of the same dollar amount in the defined time period
No transfers with the same dollar amount in the defined time period

jpfieber · October 26, 2023, 6:13pm

Interesting! Will you be releasing a Google Sheets version?

alan.heatherley · October 26, 2023, 7:01pm

I’d love to! I’m going to let a few people try this one out and see if there’s any major problems first.

I love the work you do, @jpfieber!

-Alan

randy · November 1, 2023, 12:32am

This is so cool, @alan.heatherley.

Transfer workflows are important with Tiller to balance accounts and avoid skewing cashflow calculations. Depending on transaction volume, it can sometimes be hard to identify and review transfer pairs.

You’ve done some incredible work taking a fundamental, everyday Tiller workflow and cleverly linking data effortlessly and automatically.

I’m particularly impressed with the little extras you’ve implemented in the UX:

Ability to force manual matches
Configuration for the match-lookup window
Matching at the “Transfers” GROUP level (not just the category level)
Very cool data-validation dividers in the manual-matching table
And links back to the manual matching table when matches are not found automatically

I did bump into one minor problem installing the “Transfer Helper Data” worksheet into my test sheet which I mentioned to you in a DM… but I think you’re already looking into that.

Through our Builder Rewards Program, we offer cash awards for novel and high-utility template submissions that address core workflows. Tiller is excited to award you $500.

Thanks again for creating and sharing this workflow.

alan.heatherley · November 3, 2023, 9:53pm

Hi @randy! That’s so kind of you all! I really appreciate the recognition and award.

I also made a quick video walkthrough for installing and using this solution. Take a look here on Youtube and let me know if you have any issues, questions or suggestions.

Thanks!

-Alan

randy · November 7, 2023, 4:27am

Thanks so much for making such a great installation and usage video, @alan.heatherley. Setup look pretty straightforward. It’s fun to see the solution in action and also how to resolve some of the exceptions (like multiple transactions with the same amount).

Don’t miss this cool workflow, folks.