Hello,
I am a big fan of Chandoo.org, but I am sorry to see little coverage of Power Query, which makes a lot of the working with data topics here obsolete, or at least old-fashioned. I have been working on a series of videos and blog postings about Power Query at DBExcelAccounting@blogspot.com
I have a question or rather a problem that needs solving. I have a sort of data template that I wish to create for accounting reports.
I have listed all the accounts that can be used and then explicitly determined report groupings so that the presentation in a pivot table will correspond to our accounting report.
Each account is numbered in ascending order as it is presented. then each account is assigned to a groups and sub group as needed. Some accounts do not belong to a subgroup. So the account is given as the group and subgroup and I will roll these up to have a top-level-account
All top level accounts belong to a section (revenues, expenses, assets, liabilities).
What I would like to do is create a formula that numbers the top-level account groups in a way that is consistent with the account numbering, but which will allow me to lets me sort these to be presented in the groupings I am indicating. Please see the attached file.
My thoughts are that I need and index match formula that is looking at multiple criteria and returning the first occurrence, but not sure if this can be implemented on my Account ID which has unique text, or if I must rely on sorting the rows and using row number.
Thanks in advance!
I am a big fan of Chandoo.org, but I am sorry to see little coverage of Power Query, which makes a lot of the working with data topics here obsolete, or at least old-fashioned. I have been working on a series of videos and blog postings about Power Query at DBExcelAccounting@blogspot.com
I have a question or rather a problem that needs solving. I have a sort of data template that I wish to create for accounting reports.
I have listed all the accounts that can be used and then explicitly determined report groupings so that the presentation in a pivot table will correspond to our accounting report.
Each account is numbered in ascending order as it is presented. then each account is assigned to a groups and sub group as needed. Some accounts do not belong to a subgroup. So the account is given as the group and subgroup and I will roll these up to have a top-level-account
All top level accounts belong to a section (revenues, expenses, assets, liabilities).
What I would like to do is create a formula that numbers the top-level account groups in a way that is consistent with the account numbering, but which will allow me to lets me sort these to be presented in the groupings I am indicating. Please see the attached file.
My thoughts are that I need and index match formula that is looking at multiple criteria and returning the first occurrence, but not sure if this can be implemented on my Account ID which has unique text, or if I must rely on sorting the rows and using row number.
Thanks in advance!