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

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

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.

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

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