Dear users,
I’m working in finance and would like to summarize single accounts from a list to balance sheet positions. As we get information from many different ERP systems (lots of affiliates) I have to deal with differents naming logics.
In table 1 I have all account ranges (numeric+alphanumeric) identifying a balance sheet position:
At months end I get a simple account list from each affiliate:
Now I need to assign the correct balance sheet position from my account ranges list. So I would like to add a third column to table 2 showing the balance sheet position.
Thanks for the info @Reinhard-Korn , and another thing to confirm, it does not really matter if they’re “account from” or “account to” correct? You only want to know what the balance sheet position is, correct?
If possible, could you upload these sample data? (csv or excel)
Also, please include in the table 1 (account ranges) at least what’s in the account list, in this example it’s missing all of them except for “300”, so it’s not a workable sample
I have a solution for you, but I need proper sample data
I ended up creating some sample data so I can give you the solution. I basically do a Join between the 2 tables. So here goes.
This is how the workflow looks like:
Account list is the same as the sample you have:
For Account ranges, I had to add a few entries on top of the sample of Account ranges that you provided in order to satisfy the sample of Account list you provided:
Dear bruno29a,
thanks for that, but that’s not really the solution I need. Let me explain why:
The account ranges say that all accounts, e.g. beginning from ABC100 to ABC199, are summed up to position fixed assets.
So it’s like ABC100+ABC101+ABC102+ABC103…+ABC199
The reason for using ranges is that we don’t have to map each account to a balance sheet position. So using intervals is to reduce work for maintaining the mapping table. If not using intervals our mapping table would contain thousands of datasets and would be very confusing.
Many accounting systems use fixed account ranges to identify balance sheet positions. It’s a kind of speaking numbering, so that each accountant knows, when using ABC100 or ABC102 it goes to fixed assets position. Helps to reduce posting errors and also automatic checks can be better implemented.
As we have many affiliates using different accounting systems, we now have to consolidate the data at month end. So I get a lot of account balances from each company and put them in one file. Nearly every affiliate uses its own accounting system and so different accounts with different speaking numbering systems have to be matched. If all of them would be numeric I could use simple range selection in simple row filter, but I face also alphanumeric (string) intervals.
At the end the workflow should assign each dataset (or account value) in that file to a specific balance sheet position according to our mapping table.
Please find below some example data. The file contains on the first tab the account list, on the second tab the mapping table - third tab shows desired result and fourth tab shows the data flow.
Hi @Reinhard-Korn , sorry I misunderstood your table 1, I thought Account to and Account from where account numbers representing Debit and Credit accounts.
I have a revised solution.
This is how the workflow looks like:
And using the Excel sample data you provided, this is the results I get:
This is all calculated dynamically based on the mapping table you provided, so if you add other ranges to the table, they will automatically be included.
So, a bit of details of the logic I used. I did a split between the String part and numeric part for both the data table and the account range table. For the account range table, after splitting, I assigned from from as the min value range and the to as the max value range. They were both done via Java.
The string part is stored as “Starts with”, and in case where there was no string, this would be empty.
After that, it was just a matter of joining on “Starts with” column and then apply rule engine to filter where account was between min and max.
