Eliminate or work around duplicate "Account" and "Group" column names in Accounts Sheet?

Google Sheets
1

Having duplicate column names in the Accounts sheet (“Account” and “Group”) complicates things when I’m trying to come up with a common way to determine the Column letters for the Accounts sheet. I’m looking for some best practices.

My current workaround is to change the columns we’re looking for when it’s the Accounts sheet…

=if(or(isblank(K12),isblank(L12)),iferror(1/0),REGEXEXTRACT(ADDRESS(1,MATCH(L12, if(K12=“Accounts”,indirect("’"&K12 &"’!$F$1:$1"),indirect("’"&K12 &"’!$A$1:$1")), 0)+if(K12=“Accounts”,code(“F”)-code(“A”),0)), “[A-Z]+”))

where K = the sheet name and L = the column name you’re looking for in the sheet.

The issue is that this assumes the columns with unique names I want to search for in the Accounts sheet always start in column F. If I insert any column to the left of F, this formula breaks down.

One solution would be to change the Accounts sheet Column headers so there are not duplicate header names (either A and C, and where the first Account and Group names are in my sheet; or H and P where they are duplicated). If I change H and P names, I am concerned about lack of compatible with any Community solution you download. If I change A and C, will the Tiller feed get confused on where to put new accounts?

Another option is that I think there should be a way to dynamically determine the column offset for the Accounts tab for the unique names (currently F in my Accounts sheet). This creates a circular dependency in my formula above.

I was also thinking that, if your looking for the Account and Group columns in the Accounts sheet, you pick the 2nd instance, not the first.

Looking for your best practices.

Thanks a lot,
Scott

2

I’d install some ‘solutions’ that access the Accounts sheet and see how they do it. I have the ‘Statements’ sheet installed, and it has some hidden columns on the right side used to specify the location on the Accounts sheet to grab data from.

3

I think I found a solution that will work even if someone adds columns in the Accounts sheet prior to F…

=if(or(isblank(K23),isblank(L23)),iferror(1/0),REGEXEXTRACT(ADDRESS(1,Arrayformula(SMALL(IF(indirect(K23&"!A1:1")=L23,Column(indirect(K23&"!A1:1"))-Column(INDEX(indirect(K23&"!A1:1"),1,1))+1),if(or(K23&L23=“AccountsAccount”,K23&L23=“AccountsGroup”),2,1)))),"[A-Z]+"))

Instead of Match to find the column #, use Small and pick the first column if you’re not looking for Account or Group in the Accounts sheet; otherwise, pick the 2nd column. Thanks to this post.

I know this is not needed if you never change the Unique Account Identifier column in the Accounts sheet (which is column F in my workbook). However, with this new formula; I could if I wanted to.

1 Like
4

Thanks. I have installed multiple solutions and when finding columns in the Accounts sheet, they just start with column F. Wanted some more flexibility.

5

I bumped into the same problem and came up with a different solution that is working for me. The most important part is finding the column letter from
Sheet name (G2), Column Name (H2), To right of column letter (I2).

=CHAR(CODE(I2)-1+match(H2,InDirect(G2&“!”&I2&“1:1”),0))

I put a simple version of it in

This breaks if many columns are changed in the Accounts sheet, but can be fixed without changing the formula, which is good enough for me.

6

Thanks for sharing, @TomGoBravo. I tend to use this formula in the Google Sheet’s Builder’s Guide.

7

Thank you pointing at the Builder’s Guide. That formula and the table in Tutorial: Column Mapping to Make Templates Shareable suffer from the weakness Scott asked about in the post that started this thread. I changed my demo sheet to use what Scott settled on (Small(...Column(...), ...)) instead of searching from a column letter. I then made a column “Nth” as a parameter for Small that is 1 by default, 2 for when the sheet name is “Accounts”. I’ve been using the right part of the Accounts table so this works better for me.

8

I am responsible for that doubled-up header in the Accounts sheet.

For years, I’ve had to work around the mess when I create Tiller templates. I regret not catching that earlier. We are releasing an improved Foundation Template tomorrow, but I’m afraid this problem is just too entrenched to change our way out of. Too many Tiller spreadsheets in the wild now… :person_shrugging:

I tend to just start my column-lookup header searches further to the right when referencing just the Accounts sheet, but I like what you and @Cowboy13 have done, @TomGoBravo.

Chapeau. :tophat:

1 Like