Groups in transactions list

Ask Anything
1

I would like to have a groups column in the transactions sheet in the foundation template. How would i add one which would automatically populate with the correct group for the item I have already categorized?

2

Try this (It is not the most elegant, but it works.)

After you add the new column in your Transactions sheet, add this in the first row of the new column:

=IFERROR(ARRAYFORMULA(IF(Row(H:H)=1,“Group”,(vlookup(F1:F,Categories!A:B,2)))),"")

You’ll need to check the following things in the formula to make it work:

  1. The Hs in the formula refer to your new column you added to the Transaction sheet (The column in which you put the formula). Substitute your new column’s letter for these, if it isn’t H.
  2. The formula assumes your Category column in the Transactions sheet is Column F. If it isn’t F, change out the Fs in the formula to reference the column letter in your Transaction sheet that corresponds to the Category column, as needed.
  3. The formula assumes your Categories sheet is set up with “Category” as Column A and “Group” as Column B. If not, change the formula references of A and B as needed (these need to be side by side).

There are other ways to do this that are a bit more involved (and beyond me, Ha!) Perhaps others can share that.

Cheers!

3

Hi @Brad.warren,
Your solution is a good one.

Here’s an alternate approach:

=ARRAYFORMULA(IF(ISTEXT(F:F),IFNA(VLOOKUP(F:F,Categories!A:B,2,FALSE),IFERROR(1/0)),IFERROR(1/0)))

It assumes the Transaction sheet Category column is also F. And Category and Group are the first two columns in the Categories sheet.

You don’t need to do the check for Row 1, because in the first row, Category will VLOOKUP to get Group as long as you include Row 1 in the ranges.

I use IFERROR(1/0) to generate a blank cell. If the Category is blank OR there is no match for the VLOOKUP, it will generate a blank cell. If you wanted to show a N/A where a group lookup didn’t return any values, you could use:

=ARRAYFORMULA(IF(ISTEXT(F:F),VLOOKUP(F:F,Categories!A:B,2,FALSE),IFERROR(1/0)))

Jon

2 Likes
4

Thank you both. I can see you are spreadsheet programmers. Something i havent yet learned to do. I will learn something from implementing this.

1 Like
5

Ha! Jon’s WAY ahead of me. Grateful for the upgrade!
Best.

1 Like