Merge Category Tags into Tags in Transactions Sheet

Google Sheets Show & Tell
1

What is the goal of your workflow? What problem does it solve, or how does it help you?

I want to be able to assign transaction tags based on category tags.

How did you come up with the idea for your workflow?

I wanted to be able to do reporting with more or less granularity than my categories. In the case of less granularity, I wanted to be able to have multiple categories or category groups share the same tag. In the case of greater granularity, I can add tags on the Transactions sheet.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?

I created a “Transaction Tags” column in my Transactions sheet that contains the manually managed transaction tags.

To make the Tags column dynamically aggregate the transaction and category tags, I replaced the “Tags” header on my Transactions sheet with the following formula:

=ARRAYFORMULA({"Tags"; REGEXREPLACE(REGEXREPLACE(IFERROR(VLOOKUP(INDIRECT(TransactionsCategory), {INDIRECT(CategoriesCategory), INDIRECT(CategoriesTags)}, 2, false)) & "," & INDIRECT(TransactionsTransactionTags), "^,", ""), ",$", "")})

In here, there are named ranges to dynamically lookup the correct columns, which I keep in a References sheet. For example, the cell referred to by the “TransactionsCategory” named range is defined as:

=iferror("'"&$A$6&"'!$"&char(64+match(A7,INDIRECT("'"&$A$6&"'!$A$1:$1"),0))&"$2:$"&char(64+match(A7,INDIRECT("'"&$A$6&"'!$A$1:$1"),0)))

, where A6 contains “Transactions” and A7 contains “Transaction Tags”. It resolves to “Transactions!D$2:D”, in my case.

Anything else you’d like people to know?

  • I used REGEXREPLACE above so that there wouldn’t be extra commas when one or both of the tags fields is blank. I couldn’t get TEXTJOIN to work in an ARRAYFORMULA

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

Yes

If you said yes above, please make a copy of your workflow and share the copy’s URL:

2

Pretty slick, @acjay.

It took me a few minutes to replace these range reference but then the formula worked flawlessly:

  • TransactionsCategory
  • CategoriesCategory
  • CategoriesTags
  • TransactionsTransactionTags

image
image1046×266 19.4 KB

I didn’t use the INDIRECT() cell references so in the end my formula looked like this:

=ARRAYFORMULA({"Tags"; REGEXREPLACE(REGEXREPLACE(IFERROR(VLOOKUP($D$2:$D, {Categories!$A$2:$A, Categories!$C$2:$C}, 2, false)) & "," & $F$2:$F, "^,", ""), ",$", "")})

Thanks for sharing!
Randy

3

Yeah, that’s pretty much what my original version looked like, but then I thought I should try to make it independent of column locations. Although, I suppose once set up, Sheets should handle rewriting the formula if columns are rearranged.

Another slight simplification is that in my original, I used the equivalent of VLOOKUP($D$2:$D, Categories!$A$2:$C, 3, false), rather than feeding VLOOKUP an array.

4

Adapting to column order is important to making your solution shareable— definitely a best practice for builders. I was just too lazy to setup named ranges. :wink:

5

Is this workflow the only way to get tags I’ve created in the “Categories” sheet to populate my Tags column in the “Transactions” sheet?